English

How To: Add a new user to an ArcSDE database

Summary


[O-Image] Israel
This article was contributed by Systematics Technologies R.G. Ltd., the Esri distributor for Israel.

The instructions provided describe how to add a new user to the SDE geographic database. This procedure is outlined for two common RDBMSs, Oracle Server and Microsoft SQL Server.

Procedure

Before beginning, verify that the following preconditions are fulfilled.

• ArcSDE installation has been successfully completed. (Refer to the ArcSDE installation guide for the particular RDBMS.)
• ArcSDE post installation has been successfully completed.
• The RDBMS is up and running.
• Required system administrator privileges have been obtained on the RDBMS to execute this procedure (for example, ‘sys/system’ in Oracle, ‘sa’ in SQL Server).


Oracle Server RDBMS

1. Run one of the Oracle administrative tools (Enterprise Manager Console or SQL*Plus) as the 'sys' or 'system' user.

2. Create a new tablespace to store spatial data, unless it already exists or data is to be stored in a different tablespace than SDE.

Note:
Note that this is not the tablespace created during the ArcSDE post installation, named 'sde', but a distinct tablespace. This tablespace is usually created once in the database and any user can be mapped to use the tablespace.

3. Create a new database user. The default tablespace is the one that was created in Step 2.
[O-Image] Oracle - Create  a new database user


4. Grant the appropriate roles for the new user. For standard users, grant 'Connect' and 'Resource' roles. If the new user is only a designated viewer, grant the 'Connect' role.
[O-Image] Oracle - Grant roles


5. Create a new spatial database connection (Direct connect or 3 tier connection) for this user from ArcCatalog or ArcMap.

SQL Server RDBMS


Note:
This workflow refers to the single geodatabase model, a model that stores geographic data in the same database as the SDE system tables.

1. Run SQL Server Management Studio with sysadmin privileges (i.e., 'sa').

2. Create a new login to the server instance.

A. In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.
B. Right-click the Security folder, point to New, and click Login.
C. On the General page, enter a name for the new login in the Login name box. Set the default database to the SDE database created during the ArcSDE post installation. (This database is referred to as 'sde_data' in the following screenshots.)


[O-Image] SQL - Create new login
3. Map the login to the SDE database created during the ArcSDE post installation via the 'User Mapping' page.
[O-Image] SQL - Map the login
4. Create a new schema in the database with the same name as the new user. The schema owner is the user.

A. In SQL Server Management Studio, open Object Explorer and expand the Databases folder.
B. Expand the database in which the new database schema is to be created.
C. Right-click the Security folder, point to New, and then click Schema.
D. On the General page, enter a name for the new schema in the Schema name box. This is the same as the login that was previously created.
E. In the Schema owner box, enter the name of the same user to own the schema.
[O-Image] SQL - Create a new schema

5. Define the user in the database level. Grant the new user the appropriate roles and define the default schema.

A. In SQL Server Management Studio, open Object Explorer and expand the Databases folder.
B. Expand the database.
C. Expand the Security Folder.
D. Expand the Users Folder.
E. Right-click the new user and click Properties.
F. On the General page, select the default schema created in the Step 4.
G. Grant 'public', 'db_datareader', 'db_datawriter' and 'db_ddladmin' roles to the user. If the user is merely a designated viewer, a 'public' role will suffice.
[O-Image] SQL - Define the user in the database

6. Create a new spatial database connection for this user from ArcCatalog or ArcMap to ArcSDE.