User privileges 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 geodatabase administrator.

The first table indicates the minimum user privileges needed for each type of user.

The second table lists the privileges needed to create or upgrade a geodatabase.

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

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

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

NoteNote:

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

Minimum privileges

Type of user

Database privileges

Dataset privileges

Notes

Data viewer

SELECT

If allowed to read all tables in the database, you can assign users to the db_datareader database role; otherwise, grant SELECT on specific tables and views.

Data editor

  • SELECT, INSERT, UPDATE, and DELETE on other users' datasets
  • EXECUTE on the stored procedures associated with the data to be edited

If the user will be editing versioned data through a versioned view, the user must also be granted SELECT, INSERT, UPDATE, and DELETE privileges on the versioned view. When you use the Privileges dialog box in ArcGIS to grant the SELECT, INSERT, UPDATE, and DELETE privileges on a versioned feature class, those privileges are automatically granted on the associated versioned view.

Data creator

  • CREATE TABLE
  • CREATE PROCEDURE
  • CREATE VIEW

Users who create data must have a default schema with the same name as their database user name. For example, for the user name simon, the default schema name must be simon. If it isn't, the user cannot create objects such as feature classes.

Geodatabase administrator

If the geodatabase administrator is a user named sde and that user will not own data in the geodatabase outside of the system objects, the sde user only needs to be able to connect to the geodatabase once the geodatabase has been created. However, when the geodatabase needs to be upgraded or if the sde user needs to kill connections or view all database users, additional privileges are required.

SELECT, INSERT, UPDATE, and DELETE on versioned datasets

Privileges to create or upgrade a geodatabase

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

Type of geodatabase

User and privileges to create a geodatabase

User and privileges to upgrade a geodatabase

Sde-schema geodatabase

The sde user requires the following privileges:

  • 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:

Geodatabases created prior to 10.1 and named sde are considered multiple spatial databases. Therefore, you must upgrade the geodatabase logged in as a sysadmin user.

Dbo-schema

The dbo user already has the required privileges to create a geodatabase inside a database.

The dbo user already has the required privileges 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)

NA; beginning with ArcGIS 10.1, you cannot create a multiple spatial database geodatabase.

A user (other than sde) who is in the sysadmin fixed server role must upgrade the geodatabase. Sysadmin users have the required privileges to upgrade.

Additional privileges

The following functionality requires additional privileges in the geodatabase:

Related Topics

11/14/2016