User permissions for geodatabases in SQL Server

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

The first table indicates the minimum user permissions needed in a single spatial database model geodatabase (the recommended model).

The second table lists the minimum user permissions necessary for a geodatabase stored in the multiple spatial database model.

The third table lists the permissions necessary needed to create or upgrade a geodatabase.

Additional, optional permissions needed for various geodatabase-related functionality are listed in the last section.

Dataset privileges should be granted or revoked by the dataset owner using the Change Privileges geoprocessing tool that is available in ArcGIS Desktop. See Granting and revoking privileges on datasets and Change Privileges for instructions.

You can use Management Studio to administer user database privileges. Or you can use Transact SQL statements to grant and revoke permissions.

Note:

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

Minimum 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.
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	If allowed to edit all tables in the database, you can assign users to the db_datareader and db_datawriter roles and grant EXECUTE permission at the database level.
Data creator	CREATE TABLE CREATE PROCEDURE		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.
ArcSDE administrator			The ArcSDE administrator is automatically granted the permissions it needs to work with the geodatabase when the geodatabase is created and datasets are registered as versioned. Do not revoke these privileges.

Minimum multiple spatial database model permissions

All types of users (data viewers, data editors, data creator, and ArcSDE administrators) must be added to the SDE database. If you are using shared log files, all 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.

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

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			Add as user to the other databases, but no specific required permissions

Permissions to create or upgrade a geodatabase

The following table lists the user and permissions you must use to create or upgrade geodatabases in SQL Server.

Type of geodatabase	User and permissions to create a geodatabase	User and permissions to upgrade a geodatabase
Single spatial database, sde-schema	The sde user requires the following permissions: CREATE FUNCTION CREATE PROCEDURE CREATE TABLE CREATE VIEW	The sde user must be added to the db_owner role in the database to upgrade. Alternatively, the upgrade can be run by a user (other than sde) who is in the db_owner database role or sysadmin fixed server role. Note: If the database is named sde, it is considered a multiple spatial database. Therefore, you must upgrade the geodatabase logged in as a sysadmin user.
Single spatial database, dbo-schema	The dbo user already has the required permissions to create a geodatabase.	The dbo user already has the required permissions to upgrade. Alternatively, the upgrade can be run by a user who is in the db_owner database role.
Multiple spatial database (always sde-schema)	The sde user requires the following permissions in the sde database: CREATE FUNCTION CREATE PROCEDURE CREATE TABLE CREATE VIEW	A sysadmin user (not the sde user) must upgrade the geodatabase. Sysadmin users have the required permissions to upgrade.

Additional optional permissions

The following functionality requires additional permissions in the geodatabase:

• Any user who will own an XML column must be granted REFERENCES permission on the full-text catalog used to index the XML column.
• You must grant CREATE XML SCHEMA COLLECTION to any user who will create an XML schema in the database using ArcSDE API.
• To use the sdemon command to kill direct connections to the geodatabase, the ArcSDE administrator (the sde user) in an sde-schema geodatabase must be added to the processadmin fixed server role.
• The CREATE VIEW privilege is needed for any user who must create database views, spatial views, or multiversioned views.
• If you alter your geodatabase configuration to use shared log file tables in the geodatabase, all users require CREATE TABLE permission in the database.