ArcGIS Server Banner

User permissions for geodatabases in SQL Server

User permissions for geodatabases in SQL Server
Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
Note:This topic was updated for 9.3.1.

NOTE: Applies to geodatabases created with an ArcGIS Server Enterprise license only

The following tables list the minimum required database privileges for common types of users: data viewers, data editors, data creators, and the ArcSDE administrator.

You can use Enterprise Manager (SQL Server 2000) or Management Studio (SQL Server 2005 or later) to administer user privileges. Or you can use Transact SQL statements to grant and revoke permissions.

In most cases, owners of individual datasets should use ArcCatalog to set other users' permissions on these datasets. For information on this, see Granting and revoking privileges on datasets.

Note that to perform an upgrade, the ArcSDE administrator must have write permission to either the SDEHOME/geocode directory on the server or the directory to which the TEMP environment variable is set. This is because during an upgrade, ArcSDE attempts to make backups of the locator files in the geocode directory. If that fails, ArcSDE attempts to write the backups to the TEMP directory. If the ArcSDE administrator does not have write permission to either of these directories, a warning is generated and new locators are loaded.

The first table below indicates the user permissions needed in a single spatial database model geodatabase. The second table lists the user permissions necessary for a geodatabase stored in the multiple spatial database model.

NOTE: CONNECT on database is granted to PUBLIC by default. If you revoke this privilege from PUBLIC, you will need to explicitly grant CONNECT on databases to specific roles and/or users.

Single spatial database model permissions


Type of user Database permissions Dataset permissions Notes
Data viewer

SELECT
  • If allowed to read all tables in the database, you can assign users to the db_datareader database role.

  • CREATE TABLE permission is needed if using shared log files.
Data editor

  • SELECT, INSERT, UPDATE, and DELETE on other users' data objects (if they will be editing other users' data objects)

  • EXECUTE on the stored procedures associated with the data to be edited
 CREATE TABLE permission is needed if using shared log files.
Data creator
  • CREATE TABLE

  • CREATE PROCEDURE

  • CREATE VIEW

  • Before a user can create an XML column with SQL Server 2005 or 2008, the user must be granted the REFERENCES permission on the full-text catalog in which the text of the XML documents will be indexed.

  • User names for any user accounts that own data must have the same name as their default schema. For example, for the user name simon, the default schema name must be simon. If it isn't, the user cannot create geodatabase objects such as feature classes.

  • CREATE VIEW privilege is only needed if the user will be creating database views, spatial views, or multiversioned views.
ArcSDE administrator
  • CREATE TABLE

  • CREATE PROCEDURE

  • CREATE FUNCTION

  • CREATE VIEW

  • For ArcSDE administrators, the CREATE VIEW function is required to create the DBTUNE, ST_GEOMETRY_COLUMNS, and ST_SPATIAL_REFERENCES_Systems views.

  • If you use a dbo-schema geodatabase, the database owner (dbo) is in the sysadmin fixed server role and already has these privileges.

Multiple spatial database model permissions

All types of users must be added to the SDE database. For all types of users, if you are using shared log files, users must have CREATE TABLE permissions in the SDE database. If you are using shared log files in a SQL Server 2005 or later release database, you must create a schema for the users when you add them to the SDE database.


Type of user SDE database permissions Dataset permissions in SDE database Permissions in other databases Dataset permissions in other databases
Data viewer

SELECT

SELECT
Data editor

SELECT, INSERT, UPDATE, and DELETE permission on datasets to edit and EXECUTE permission on related stored procedures

SELECT, INSERT, UPDATE, and DELETE permission on datasets to edit and EXECUTE permission on related stored procedures
Data creator CREATE TABLE and CREATE PROCEDURE if creating datasets in the repository database (not recommended)

CREATE TABLE and CREATE PROCEDURE

ArcSDE administrator CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, and CREATE FUNCTION

Add as user to the other databases, but no specific required permissions

For more information on the differences between the single and multiple spatial database models in SQL Server databases, see Using multiple geodatabases in SQL Server.