ArcSDE DBO and SDE schemas on SQL Server
Geodatabases stored in SQL Server can be owned by a user named sde or a user who is dbo in the database. Let’s dig into this a little to figure out the differences.
First, a few facts about SQL Server logins, users, principals, schemas, and roles. If you already know this stuff, you can skip down to the next major section:
Logins, users, principals, schemas, and roles, oh my!
1. A login an entity that can gain access to the server. By server, I don’t mean the machine or the web server on the machine, I mean the ‘SQL Server Instance’.
SQL Server security is has two security realms: the server and the database. Logins give you access to the server and they come in two different flavors: Windows Authentication mode or SQL Server and Windows Authentication mode. Since logins gain access to the server, they also define permissions across the whole server (across multiple databases).
NOTE: Server level permissions like “ALTER ANY CREDENTIAL” AND “VIEW ANY DEFINITION” are the permissions assignable to logins.
2. A user is an entity that can gain access to a database. Users need access to the server before accessing a database, so they are mapped to logins by an SID property that both entities hold. As you can see, the terminology of logins and users should not be used interchangeably: Logins are for security of the server realm and users are for security of the database realm. Thus it’s possible to create orphaned users when they get out of sync with the logins on the server. If you move a database from one server to another, the users in the database will be orphaned until you remap them to valid logins on the other server.
NOTE: Database level permissions like “CREATE TABLE” and “BACKUP DATABASE” are the permissions assignable to users.
3. A principal is any entity that can be granted a permission. Since the logins and users we discussed above can be granted permissions, they are also principals. SQL Server has 6 types of server principals and 7 types of database principals, listed below:
| Server Principals | Database Principals |
|---|---|
| S = SQL login | S = SQL user |
| U = Windows login | U = Windows user |
| G = Windows group | G = Windows group |
| R = Server role | A = Application role |
| C = Login mapped to a certificate | C = User mapped to a certificate |
| K = Login mapped to an asymmetric key | K = User mapped to an asymmetric key |
| R = Database role |
4. A schema is simply container of objects, such as tables, views, stored procedures, etc.
5. A role is a security principal that groups other principals. SQL Server provides nine fixed server roles, such as sysadmin and serveradmin.
Back to ArcSDE
Now that you know the very basics on SQL Server Security, let’s get back to your choice of setting up an SDE Schema geodatabase or a DBO Schema geodatabase.
Multiple versus Single
In the screenshot above you’ll see that the SDE Schema can support a multiple spatial database instance, DBO does not, and multiple is better, right? Well, not so fast…
Beginning with version 9, ArcSDE started supporting both multiple and single models (prior to 9, you could only use multiple). Now that ArcSDE supports a single database model, the multiple model (though still supported) has been deprecated.
What’s the difference between them? In the multiple database model, the ArcSDE Admin’s user schema would sit in its own database called SDE. Meanwhile, user-defined data, like feature classes, raster tables, and nonspatial tables, would reside in other databases. It’s called a multiple model because multiple SQL Server databases are used to make one ArcSDE geodatabase. The single model puts everything in one database.
Note that you can still have multiple geodatabases on the same machine even if you opt for the (recommended) single model. SQL Server supports multiple databases within a single SQL Server instance. If you want to create more than one geodatabase when using the single spatial database model, just create them as separate SQL Server databases. Use the ArcSDE post installation wizard to create each one. (Run it multiple times.)
SDE versus DBO Ownership
If you create an SDE Schema geodatabase, it’s going to be owned by a user named “sde”. If you create a DBO Schema geodatabase, it’s going to be owned by the “dbo” user that ships with SQL Server. There’s no difference in the performance or functionality between the two types of geodatabase schemas, but they do have different security implications. Your choice is going to depend on how you administer databases.
DBO
If your server is not configured to support “SQL Server and Windows Authentication mode”, a Windows account named “sde” will be required to login as “sde”. The sde user can login as a Windows user or a SQL Server user, but only if mixed-mode authentication is turned on. Some people don’t want mixed-mode enabled. For them, DBO is easier to setup: no dedicated Windows account required.
Another reason to use DBO is that multiple Windows logins can map to the dbo user. You can make any user an SDE admin by adding them to the sysadmin or db_owner role. Each admin will have permission to perform ArcSDE tasks such as compress, and with Windows authentication, they can use their standard network username and password to do it. People tend to write down the sde password if they don’t use it everyday. Reusing Windows credentials can be more secure.
SDE
If the server needs to restrict SDE administrators to certain databases and not others, an SDE Schema will provide that. Unlike the dbo user, which usually has server-wide access to all databases, the sde user can be restricted to just a handful of permissions within a specific database. Thus, the SDE administrator for an SDE Schema geodatabase can be someone without the sysadmin or db_owner role. If you don’t want wide-spread permissions for geodatabase admins, use SDE Schema. The POST installation wizard will give “sde” only the minimum permissions it needs.
Well, that’s a LOT of words to explain one small configuration, but it’s an important choice with security implications you won’t know unless you understand both ArcSDE and SQL Server security. Hopefully you understand them now. Good luck!
We had a great time at this year’s Dev Summit. It’s a huge conference with a lot of concurrent sessions. There’s no way we could cover it all, but here are some of the highlights:
