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 topic 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 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.
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 |
| 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 |
| 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. | SELECT, INSERT, UPDATE, and DELETE on versioned datasets |
Additional privileges are required to upgrade the geodatabase or to allow the sde user to drop or view all connections. |
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:
| 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. |
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. The multiple spatial database geodatabase is deprecated; you cannot create one. | NA. You can no longer upgrade these geodatabases. Migrate to a single-database geodatabase. |
Additional privileges
The following functionality requires additional privileges in the geodatabase:
- The geodatabase administrator (the sde user) in an sde-schema geodatabase 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.
- Any user who will own an XML column must be granted REFERENCES privileges on the full-text catalog used to index the XML column.
- If you alter your geodatabase configuration to use shared log file tables in the geodatabase, all users require CREATE TABLE privileges in the database.