Creating the administrator geodatabase users and connection—Help

Available with Production Mapping license.

Creating the administrator geodatabase users
Creating the administrator database connection

Creating the administrator geodatabase users

You can choose to add users to a geodatabase in SQL Server through Microsoft SQL Server Management Studio. The geodatabase users are underlying enterprise database users and store the product library feature classes and tables. You need to grant the appropriate permissions.

For more information, see Add logins and users to SQL Server.

For product library administrators, the following is suggested:

User type	Role	SQL Server permissions
Data Creator	CONNECT	CREATE TABLE CREATE VIEW CREATE PROCEDURE

User type

Role

SQL Server permissions

Data Creator

CONNECT

CREATE TABLE
CREATE VIEW
CREATE PROCEDURE

Administrator permissions

For more information, see Privileges for geodatabases in SQL Server.

The following scripts can be used in Microsoft SQL Server Management Studio to create a new database user to store the product library feature classes and tables, and grant the appropriate permissions.

Create user and schema:

--use prodlibdb database
USE [prodlibdb]
GO
EXEC sp_addlogin 'prodlib', 'prodlib', @logindb, @loginlang
GO
CREATE USER [prodlib] FOR LOGIN [prodlib]
GO
CREATE SCHEMA [prodlib] AUTHORIZATION [prodlib]
GO
ALTER USER [prodlib] WITH DEFAULT_SCHEMA = [prodlib]
GO

Grant privileges:

--use prodlibdb database
USE [prodlibdb]
GO
EXEC sp_droprolemember 'gis_data_creator', 'prodlib'
GO
EXEC sp_droprole 'gis_data_creator'
GO
CREATE ROLE gis_data_creator AUTHORIZATION dbo
GO
GRANT CREATE TABLE TO gis_data_creator
GO
GRANT CREATE PROCEDURE TO gis_data_creator
GO
GRANT CREATE VIEW TO gis_data_creator
GO
EXEC sp_addrolemember 'gis_data_creator', 'prodlib'
GO

Verify roles:

EXEC sp_helprolemember 'gis_data_creator'
GO

Verify role permissions:

select dp.NAME AS principal_name,
 dp.type_desc AS principal_type_desc,
 o.NAME AS object_name,
 p.permission_name,
 p.state_desc AS permission_state_desc 
 from sys.database_permissions p
 left OUTER JOIN sys.all_objects o
 on p.major_id = o.OBJECT_ID
 inner JOIN sys.database_principals dp
 on p.grantee_principal_id = dp.principal_id
 where dp.NAME = 'gis_data_creator'
GO

Verify user permissions:

select USER_NAME(p.grantee_principal_id) AS principal_name,
 dp.type_desc AS principal_type_desc,
 p.class_desc,
 OBJECT_NAME(p.major_id) AS object_name,
 p.permission_name,
 p.state_desc AS permission_state_desc 
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
where USER_NAME(p.grantee_principal_id) = 'prodlib'

Associate Login prodlib with User prodlib:

--use prodlibdb database
USE [prodlibdb]
GO
EXEC sp_change_users_login 'update_one','prodlib','prodlib'
GO
EXEC sp_helpuser 'prodlib'

Creating the administrator database connection

You need to create an administrator database connection when the product library is stored in an enterprise geodatabase. Create a database connection in ArcCatalog with the PRODLIB user; this will be the product library workspace location.

For more information, see Database connections in ArcGIS for Desktop.