ArcSDE for SQL Server dbo-schema geodatabases
ArcSDE for SQL Server dbo-schema geodatabases
|
| Release 9.3 |
   |
In past releases of ArcSDE, there was only a single ArcSDE administrator user—SDE. This user was required in every database that housed ArcSDE metadata. Starting with ArcGIS 9.2, a second ArcSDE administrator user was introduced for SQL Server—dbo. The dbo user is any user in SQL Server who is a member of the sysadmin fixed server role in the database. For example, sa is a sysadmin and, therefore, a dbo user.
If you want to use a geodatabase that is owned by dbo, you must use 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 and is the only type available when creating a geodatabase in SQL Server Express. The topic Using multiple geodatabases in SQL Server explains the difference between single spatial database instances and multiple spatial database instances.
Administrator schema
The ArcSDE metadata resides on the schema belonging to the ArcSDE administrator. If the metadata resides on the SDE schema, it is referred to as an SDE-schema geodatabase. If the metadata resides on the dbo schema, it is referred to as a dbo-schema geodatabase. In SQL Server 2000, the term schema is synonymous with the term owner. This changed beginning with SQL Server 2005. See Users and schemas on SQL Server 2005 for an explanation of this change and what it means for geodatabase setup.
An ArcSDE administrator is chosen when the ArcSDE software is installed and the ArcSDE Post Installation wizard is run. If the repository setup from the wizard or the sdesetup command is run as the SDE user, the metadata will be created on the SDE schema. This applies only to ArcSDE geodatabases for SQL Server licensed through ArcGIS Server Enterprise.
Similarly, if the repository setup or sdesetup command is executed by a dbo user—any user that is part of the sysadmin fixed server role in the database—the metadata will be created on the dbo schema. As stated before, geodatabases created in SQL Server Express will always use have a dbo-schema and the administrator will always be dbo.
Deciding whether to use a dbo-schema geodatabase
There are benefits and drawbacks to either type of ArcSDE administrator user. If you are using an ArcSDE geodatabase licensed through ArcGIS Server Enterprise, choose the user best suited to your system. There is no difference in the performance or functionality of either.
Note that geodatabases on a database server (SQL Server Express instance) always use a dbo-schema, so there is no decision to make here.
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 and the person who will do most of the administration of the geodatabase is not a dbo user, you will 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 with a specific database.
Creating an ArcSDE service
NOTE: Applies to geodatabases created with an ArcGIS Server Enterprise license only
The ArcSDE service now includes an additional registry keyword, SDE_DBA_USER, to provide the ArcSDE service with the login name of the user. This information is used by the service when it connects to SQL Server. The default is the sde user name. If you are manually creating a service against a dbo-schema geodatabase, you must configure this keyword using the sdeservice –o register command. Set the name to the login of the dbo user (not the user name). For example:
sdeservice –o register –r SDE_DBA_USER –v WORLD\John –i ESRI_sde –p pass –d SQLSERVER
In this example, WORLD\John is the name of the login that maps to the dbo user in the database. It is this login that will connect to SQL Server via the giomgr.exe process when the service starts.
Connecting to a dbo-schema geodatabase
When connecting to a dbo-schema geodatabase from ArcCatalog, you must manually change the version to which you are connecting. The default version is sde.DEFAULT. In a dbo-schema geodatabase, the default version is now dbo.DEFAULT. However, ArcGIS does not have a way of determining that until a connection has been made.
This means, when you create a new connection to a dbo-schema geodatabase, you must change the version to which you are connecting.
To do this, click the Change button and choose the dbo.DEFAULT version from the list of transactional versions.
