A comparison of geodatabase owners in SQL Server

Geodatabases stored in Microsoft SQL Server can be owned by a user named sde or a user who is dbo in the database.

The sde user is a database or Windows-authenticated user. The sde user must be granted permissions that allow the user to do administrative tasks in the geodatabase.

The dbo user is any user in SQL Server who is a member of the sysadmin fixed server role. For example, sa is a sysadmin and, therefore, a dbo user. This user automatically has the permissions needed to do administrative tasks in the geodatabase.

You choose which user owns the geodatabase when you create it by choosing either a dbo or sde schema geodatabase.

If the system tables are stored in the schema of the sde user, the geodatabase is referred to as an sde-schema geodatabase. If the system tables are stored in the schema of a dbo user, the geodatabase is referred to as a dbo-schema geodatabase.

Note:

Any user who owns data, including geodatabase system tables, must have a schema with the same name as the user name.

There is no difference in the performance or functionality between the two types of geodatabase schemas. Each has benefits and drawbacks. Choose the user best suited to your system.

The following is a comparison of the two types of schemas:

• If you are running on a server that only supports Windows Authentication, ArcSDE setup and administration is greatly simplified by creating a dbo-schema geodatabase because there is no need to configure a special Windows sde login. Also, anyone who maps to the dbo user in the database can perform ArcSDE administrative tasks, such as compress.
• If you have a more restrictive security model, you might want to configure an sde-schema geodatabase. Unlike the dbo user, which usually has server-wide access to all databases, the sde user can be restricted to just a handful of statement permissions within a specific database.
• Dbo-schema geodatabases can only be used with the single spatial database model. The single spatial database model holds all the geodatabase tables—system tables and user tables—in one database. This is the recommended model to use for your geodatabase in SQL Server. However, the multiple spatial database instance is still supported. This type of geodatabase requires you to use an sde-schema geodatabase.