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.

NoteNote:

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.

NoteNote:

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:

Related Topics


8/19/2013