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:
- Geodatabase system tables, triggers, views, and procedures
- The DEFAULT geodatabase version
Because the geodatabase administrator owns all the geodatabase system files, directories, and tables, the geodatabase administrator account must be created before you can create a geodatabase. To create an sde-schema geodatabase, you must have a user named sde in the database. 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 Create Enterprise Geodatabase, and the tool creates a SQL Server-authenticated sde login, sde database user, and its schema. 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 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 database administrator 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 database administrator 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 database administrator 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 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.
Esri recommends that the geodatabase administrator's schema only be used to store the geodatabase repository. You should create separate users and schemas to store datasets such as feature classes and tables. Following the practice of storing only geodatabase system objects in the geodatabase administrator's schema 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.