Granting privileges to NIS components using a script

Since the NIS XML was imported into the geodatabase, you need to assign permissions in the new NIS workspace to the editor and viewer database roles and assign the roles to the individual users.

Note:

Copying and pasting the examples may cause syntax errors.

1. In SQL Server Management Studio, grant permissions to the NIS workspace tables through the schema.

   The sample script shows how to remove existing members from a role and drop the roles, re-create the roles, and assign permissions to the role through the schema.

   USE [nisdb]
   GO
   EXEC sp_droprolemember 'rlniseditor', 'nis_editor'
   GO
   EXEC sp_droprole 'rlniseditor'
   GO
   EXEC sp_addrole 'rlniseditor', 'nis'
   GO
   GRANT DELETE ON SCHEMA::[nis] TO [rlniseditor]
   GRANT EXECUTE ON SCHEMA::[nis] TO [rlniseditor]
   GRANT INSERT ON SCHEMA::[nis] TO [rlniseditor]
   GRANT SELECT ON SCHEMA::[nis] TO [rlniseditor]
   GRANT UPDATE ON SCHEMA::[nis] TO [rlniseditor]
   GO
   EXEC sp_droprolemember 'rlnisviewer', 'nis_viewer'
   GO
   EXEC sp_droprole 'rlnisviewer'
   GO
   EXEC sp_addrole 'rlnisviewer', 'nis'
   GO
   GRANT SELECT ON SCHEMA::[nis] TO [rlnisviewer]
   GO
   

2. Use the following script to list the roles so that you can verify them.

   EXEC sp_helprolemember 'rlniseditor'
   GO
   EXEC sp_helprolemember 'rlnisviewer'
   GO
   

3. Use the following script to list the privileges assigned to the role so that you can 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 in ('rlniseditor', 'rlnisviewer')
   GO