ArcSDE database servers are SQL Server Express instances that store ArcSDE geodatabases. There are three types of user permissions for ArcSDE database servers: database server permissions, geodatabase permissions, and dataset permissions. Each of these, plus how to alter these permissions, is described in this topic.
Database server permissions
—The only permission that can be set at the database server level is the ArcSDE database server administrator; you either are one or you are not. The ArcSDE database server administrator can do the following:
- Add and remove users to/from the database server.
- Manage geodatabase and user security.
- Create and delete geodatabases.
- Attach and detach geodatabases.
- Back up and restore geodatabases.
- Upgrade geodatabases.
- Compress geodatabases.
- Start, stop, and pause the database server.
During postinstallation setup, a database server administrator user is added. This original database server administrator can assign other users to the Server administrator role from the database server Permissions dialog box. Typically, you won't have more than one or two database server administrators.
Any data created by a database server administrator will be created in the database owner (dbo) schema. This has implications for naming data and administering permissions on specific database objects.
Being a database server administrator automatically gives you administrative rights to all geodatabases on the database server as well as read/write permission on all objects in each geodatabase.
Below is an example of the Permissions dialog box for database servers:
NOTE: When installing on Windows 2000, Server 2003, or XP, users who are active directory administrators or are local Windows administrators on the computer on which the SQL Server Express instance is installed will automatically have access to all instances on that machine without you having to add them and will automatically be database server administrators. This is a SQL Server feature. This is not true, however, when installing on Windows Vista or Windows Server 2008. Please consult the SQL Server books online if you would like details on this.
—Users who are not administrators at the database server level can be granted permissions to specific geodatabases. Geodatabase-wide permissions are granted by a database server administrator and are managed using roles. Possible roles to which a user can be assigned are
- Read Only—Allows the user to select from every table in the geodatabase.
- Read/Write—Allows the user to select and edit all existing data and create new geodatabase objects such as feature classes. If a user is given this geodatabase-wide permission, you cannot change his or her permission at the dataset level; it is automatically Read/Write.
- Admin—Allows the user to be an administrator on that geodatabase only. This means he or she has read/write permissions to all the datasets in the geodatabase and those rights cannot be taken away at the dataset level. For example, you could not open the Permissions dialog box at the dataset level and choose Read Only rights for that user on a dataset. Similarly, if you make a connection to the geodatabase through the Database Connections folder, changing the user's dataset rights to SELECT will not make that dataset read-only to the geodatabase administrative user.
In addition to having read/write access to the datasets in the geodatabase, geodatabase administrators can perform administrative tasks on that geodatabase including creating geodatabase backups, compressing the geodatabase, upgrading the geodatabase, and administering other users' rights on the geodatabase. (Users must already exist on the database server; geodatabase administrators cannot add users to the database server.)
The other option for user roles is None. In this case, the user has no geodatabase-wide permissions; however, you may still grant this user Read Only or Read/Write permissions to specific datasets, as discussed below. None is the default level of geodatabase permission granted when users are added to the database server.
In the sample geodatabase Permissions dialog box below, user pllama is granted Read/Write permissions on geodatabase historical.
—Possible dataset permissions available through the Permissions dialog box at the dataset level are Read Only, Read/Write, and None. For example, you might want to give users in an analyst group read-only permissions to a geodatabase but grant them read/write permissions to one specific feature class in the geodatabase. Or a user may have no geodatabase-wide permissions (None) but can still be granted read or read/write permission to specific feature datasets in the geodatabase.
When a user creates a dataset, such as a table, it is owned by that user and considered part of that user's schema. User permissions on datasets within a geodatabase can only be set by the owner of the dataset.
In the case of a database server administrator, the datasets he or she creates are owned by dbo and stored in the dbo's schema. The database server administrator, therefore, can grant permissions on any datasets in the dbo schema, but only on objects in the dbo schema. In other words, a database server administrator cannot grant permission to data owned by nonadministrative users.
The following is an example of the dataset Permissions dialog box:
If you create a connection to the geodatabase in the Database Connections folder of the Catalog tree, you also have access to the Privileges dialog box for datasets. To learn more about administering dataset permissions in the Privileges dialog box, see Granting and revoking privileges on datasets