In geodatabases in a Microsoft SQL Server database, the tables, views, functions, and stored procedures that compose a geodatabase 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 for 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.
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.
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.
Comparison table
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 |
|
|
Dbo (mapped to dbo in specific database) | Operating system or SQL Server login |
|
|
Sde | SQL Server login |
|
|
Sde | Operating system login |
|
|