Privileges for geodatabases in SQL Server

Privileges determine what someone is authorized to do with the data and the database. Privileges should be assigned based on the type of work the person does within the organization. Is this person involved with administration of the geodatabase? Does he or she need to edit or create data? Or would this person only need to query the data?

User privileges are set at different levels. This page lists database and dataset privileges for common types of users: data viewers, data editors, data creators, and the geodatabase administrator.

• The first section indicates the minimum privileges needed for each type of user.
• The second section lists the privileges needed to create or upgrade a geodatabase.
• Optional privileges needed for additional geodatabase functionality are listed in the last section.

You can use Microsoft SQL Server tools or Transact SQL statements to manage user database privileges.

Privileges on datasets in geodatabases should be granted or revoked using ArcGIS clients and must be done by the dataset owner.

Note:

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

Privileges listed in this page apply to SQL Server and to SQL Server database services supported by ArcGIS.

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	When you use ArcGIS to grant the SELECT, INSERT, UPDATE, and DELETE privileges on a feature class or table registered for traditional versioning, those privileges are automatically granted on the associated versioned view. These privileges are required for the user to edit using a SQL and a versioned view.
Data creator	CREATE TABLE CREATE PROCEDURE CREATE VIEW Each user must own or have CONTROL permission on a schema that has the same name as their username, and the schema must be the user's default schema.		Schema names must match individual usernames. You cannot use a single schema for all data creators. For example, the username simon must own or control a schema named simon. If this requirement is not met, the user cannot create objects such as feature classes.
Geodatabase administrator	The sde user only needs to be able to connect to the geodatabase once the geodatabase has been created or upgraded.	SELECT, INSERT, UPDATE, and DELETE on versioned datasets	Additional privileges are required to allow the sde user to drop or view all connections in SQL Server, Amazon Relational Database Service (RDS) for SQL Server, and Google Cloud SQL for SQL Server. No additional privileges need to be granted to allow the sde user to drop connections from a Microsoft Azure SQL Managed Instance. The sde user cannot drop connections to Azure SQL Database.

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 and SQL Server database services:

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 own or have CONTROL permission on a schema named sde, and the sde schema must be the sde user's default schema. If the database options READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION are not set to ON before the geodatabase is created, the sde user requires the ALTER privilege. For geodatabases in Microsoft Azure SQL Database, the VIEW DATABASE STATE privilege is also required.	The sde user must be added to the db_owner role in the database to upgrade. Alternatively, the upgrade can be run in SQL Server by a user (other than sde) who is in the db_owner database role or sysadmin fixed server role. This does not apply to database services.
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.

Additional privileges

The following functionality requires additional privileges in the geodatabase:

• The geodatabase administrator (the sde user) in an sde-schema geodatabase in SQL Server must be added to the processadmin fixed server role and granted VIEW DEFINITION privileges on the database to drop geodatabase connections. The Create Enterprise Geodatabase geoprocessing tool grants this role and privilege to the sde user. You can revoke this privilege and remove the user from the processadmin role after geodatabase creation, but if you do, the sde user will not be able to disconnect users from the geodatabase.
• The sde user in Google Cloud SQL for SQL Server and Amazon RDS for SQL Server must be added to the processadmin fixed server role to drop geodatabase connections.
• Any user who will own an XML column in SQL Server must be granted REFERENCES privileges on the full-text catalog used to index the XML column.