The geodatabase administrator in SQL Server

In Microsoft SQL Server, the geodatabase administrator can be either a user named sde or a login that is mapped to the dbo user in the database that contains the geodatabase. The geodatabase administrator is responsible for the administration of the following:

• ArcSDE geodatabase system tables, triggers, and procedures
• The ArcSDE service (if one is used)
• Geodatabase versions

Because the geodatabase administrator owns all the geodatabase system files, directories, and tables, the geodatabase administrator account must be created before the geodatabase can be created. To create an sde-schema geodatabase, you must have a user named sde. To create a dbo-schema geodatabase, you must connect with a login that is the dbo user in the database. You have the following options for creating the geodatabase administrator and geodatabase:

• If you want ArcGIS to create an sde database-authenticated login and user for you, use the Create Enterprise Geodatabase geoprocessing tool or a Python script. Choose an sde-schema geodatabase when running the Create Enterprise Geodatabase tool or script, and a SQL Server-authenticated sde login, sde database user, and its schema are created. The tool grants to the sde user the privileges necessary to create a geodatabase and kill connections. The tool also creates a database and enables geodatabase functionality in the database.
• If you want to create a dbo-schema geodatabase, you can use the Create Enterprise Geodatabase geoprocessing tool or script or the Enable Enterprise Geodatabase geoprocessing tool or script. In either case, the database administrator (DBA) must add to the SQL Server instance the login that will be used when creating the geodatabase. This login can be a Windows-authenticated login or SQL Server-authenticated login. Also, the login must either be the database owner (in which case, the DBA must create the database before you create the geodatabase), or the login must be a member of the sysadmin fixed-server role. Once the login has been added to the SQL Server instance, you can connect with that login to create the geodatabase. If the database has not been created yet, run the Create Enterprise Geodatabase tool or script to create the database and geodatabase. If the database already exists, run the Enable Enterprise Geodatabase tool or script.
• If you want to use a Windows-authenticated sde user, the DBA must create the database and sde database user and schema, set the sde schema as the default schema for the sde user, grant the sde user the privileges required to create a geodatabase, and map the sde user to the appropriate Windows-authenticated login. Then, you can log in with the Windows-authenticated login and run the Enable Enterprise Geodatabase geoprocessing tool or a Python script to create an sde-schema geodatabase in the SQL Server database the DBA created.

For more information on the two types of geodatabase administrators, see A comparison of geodatabase owners in SQL Server.

The geodatabase administrator and schema

In SQL Server, users do not automatically have a schema with the same name as their user name. However, to use SQL Server with ArcGIS, your user names must have corresponding default schema names. This applies to the geodatabase administrator user as well as nonadministrative users who create data. In the case of dbo users, a dbo schema is present in all databases by default; any login that is dbo in the database will store data in the dbo schema. If you use a geodatabase administrator named sde, it must own a schema named sde, and that schema must be the sde user's default schema. As mentioned previously, the Create Enterprise Geodatabase tool creates the sde user's schema if you use a SQL Server-authenticated sde user. If you create the sde user manually, you must also create an sde schema.

It is recommended that the geodatabase administrator and its schema only be used to manage and store the ArcSDE geodatabase repository. You should create separate user schemas in which to store your data objects, such as feature classes and raster datasets. Following the practice of storing only system tables in the geodatabase administrator's storage space simplifies the management of the geodatabase and makes it easier for you to delete the geodatabase from your database if you ever decide to do so.