A comparison of geodatabase owners in SQL Server

Geodatabases are a collection of tables, views, functions, and stored procedures inside a database. In geodatabases in a Microsoft SQL Server database, this collection of objects can be owned by a database user named sde or the dbo database user. Whichever user owns the geodatabase is considered the geodatabase administrator. Because user names and schema names must match in a geodatabase, geodatabases owned by the sde user are called sde-schema geodatabases and geodatabases owned by dbo are called dbo-schema geodatabases.

Note:

It is important that you understand how SQL Server manages access to data and other objects. Therefore, if you are unfamiliar with the SQL Server security model, read the Microsoft SQL Server documentation. SQL Server authenticates a login at the instance level and authorizes a corresponding user at the database level. Different privileges granted can apply to the entire instance, a specific database or databases, or data within a database. This could affect your decision on which type of geodatabase owner to use.

The login you connect with when you create the geodatabase determines which database user owns the geodatabase. If the operating system login or SQL Server login you connect with is mapped to the dbo user in the database, a dbo-schema geodatabase is created. If the operating system or SQL Server login you connect with is mapped to a user named sde in the database, an sde-schema geodatabase is created.

The sde user

The sde user in a database can be associated with a SQL Server authenticated login or an operating system-authenticated login. The sde user must have authority on a schema named sde, and that schema must be the sde user's default schema. The sde user also must be granted privileges in the database that allow the user to create and administer the geodatabase.

The dbo user

The dbo user and its default schema exist in all databases automatically. Logins can be dbo in a database in one of two ways:

• By creating or being made owner of a specific database
• By being a member of the sysadmin fixed-server role

Logins that are mapped to the dbo user in a specific database have the highest possible privileges in that database; therefore, they have privileges sufficient to create and administer the geodatabase. Logins that are mapped to dbo in a specific database do not have elevated privileges in the SQL Server instance or other databases unless such privileges are explicitly granted to the login.

Logins that are members of the sysadmin fixed-server role are mapped to dbo in every database on the SQL Server instance and also have the highest possible privileges throughout the SQL Server instance. Such logins have privileges sufficient to create and administer the geodatabase and can create, alter, delete, and administer other securables in the instance.

All database objects owned by the dbo user are stored in the dbo schema.

Which user should own the geodatabase?

There is no difference in the performance or functionality between the two types of geodatabase schemas. Each has benefits and drawbacks. Choose the user (and, consequently, schema) best suited to your system and chosen security model.

The following is a comparison of the two types of schema, based on the type of authentication you use:

Schema	Authentication	Pros	Cons
Dbo (member of sysadmin)	Operating system or SQL Server login	If the SQL Server database administrator also serves as the geodatabase administrator, it might make sense to use a dbo schema to avoid having the same person use two different logins depending on what task he or she needs to do. If more than one geodatabase administrator is needed, multiple logins can be added to the sysadmin fixed-server role.	The login has elevated privileges on all securables in the SQL Server instance. The login must be created and added to the sysadmin role prior to running the Create Enterprise Geodatabase tool. If using a local rather than a domain login, the login only exists on the server where SQL Server is installed. Therefore, ArcGIS clients must be installed on the same server, and all geodatabase administration tasks must be performed while logged in as the local login on that server. Dbo-schema geodatabases are not supported in Amazon Relational Database Service (RDS) for SQL Server.
Dbo (mapped to dbo in specific database)	Operating system or SQL Server login	The geodatabase administrator can perform geodatabase and database administration in the specific database. Elevated privileges do not extend beyond the specific database. If additional geodatabase administrators are needed, other logins can be placed in the sysadmin fixed-server role, making them dbo in this database as well.	The user has elevated privileges in the database. The login and the database must be created prior to running the Create Enterprise Geodatabase tool, and the login must be set as the owner of the database. If using a local rather than a domain login, the login only exists on the server where SQL Server is installed. Therefore, ArcGIS clients must be installed on the same server, and all geodatabase administration tasks must be performed while logged in as the local login on that server. Dbo-schema geodatabases are not supported in Amazon RDS for SQL Server.
Sde	SQL Server login	The sde user only requires a few statement permissions within a specific database to administer the geodatabase. An sde SQL Server login, database user, and schema can be created with the Create Enterprise Geodatabase tool when the geodatabase is created in the database. Alternatively, the database administrator can create the database, sde user (with required privileges), and sde schema and let the sde user create the geodatabase using the Enable Enterprise Geodatabase geoprocessing tool. An sde SQL Server login can be used when creating a geodatabase in Amazon RDS for SQL Server.	Only a single login can map to the sde user. The SQL Server instance must allow mixed-mode authentication. The Create Enterprise Geodatabase tool must be run by the database administrator.
Sde	Operating system login	The sde user only requires a few statement permissions within a specific database to create and administer the geodatabase. The database administrator can create the database, sde user (with required privileges), and sde schema and let the sde user create the geodatabase using the Enable Enterprise Geodatabase geoprocessing tool. You can map an existing domain login to the sde user. An operating system-authenticated sde login can be used at sites where the SQL Server instance allows only operating system authentication.	The creation and management of an operating system login that is not directly associated with a specific person might be required.* The database, sde login, user, and schema must be created prior to running the Create Enterprise Geodatabase tool. Only a single login can map to the sde user. You must be logged in to Windows with the sde login to run the Create Enterprise Geodatabase tool. If using a local rather than a domain login, the login only exists on the server where SQL Server is installed. Therefore, ArcGIS clients must be installed on the same server, and all geodatabase administration tasks must be performed while logged in as the local login on that server.

*Most operating system logins, especially domain logins, belong to a specific person. That login is used by that person to log in to his or her computer and access applications, including SQL Server. Therefore, that person will always connect to the geodatabase as the sde user. If the same person acts as both the geodatabase administrator and creates data in the geodatabase, you may need to create another operating system login and map that to a different, lower-privileged user in the database, essentially giving the person two logins. That means the person must switch logins on the computer depending on what task needs to be completed in the geodatabase. Many sites avoid creating multiple logins for the same person, as it can have security implications and increases the complexity of login management. For these reasons, an operating system-authenticated sde user is not a good option if the same person fills multiple roles in the geodatabase.