Granting privileges to product library components using a script (Production Mapping)

Read/Write privileges are already assigned to all the product library tables, except the PCAT_PERMISSION which doesn't need them. This is done when the product library is defined or upgraded. If a product library XML was imported into a geodatabase, or distributed, you need to re-create the CKB_USERS role and assign permissions in the new product library geodatabase.

The PCAT_PERMISSION table only needs read privileges assigned to it.

Use the following script to re-create the CKB_USERS role and grant the correct permissions:

Note:

Copying and pasting the example may cause syntax errors.

/*RECREATE ckb_users role */
--use prodlibdb database
USE [prodlibdb]
GO
EXEC sp_droprolemember 'ckb_users', 'prodlibuser'
GO
EXEC sp_droprole 'ckb_users'
GO
EXEC sp_addrole 'ckb_users', 'prodlib'
GO
EXEC sp_addrolemember 'ckb_users', 'prodlibuser'
GO
DECLARE @OWNER varchar(10)
SET @OWNER = 'PRODLIB'
DECLARE Tables_Cursor CURSOR
READ_ONLY
FOR SELECT a.name as table_name, a.xtype as type 
    FROM dbo.sysobjects a, dbo.sysusers b
    WHERE a.uid = b.uid and a.xtype in ('U','P') and b.name = @OWNER ORDER BY a.name
DECLARE @name varchar(100), @type varchar(1)
OPEN Tables_Cursor
FETCH NEXT FROM Tables_Cursor INTO @name, @type
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
   BEGIN
      --PRINT @owner + '.' + @name + ' ' + @type
      -- GRANT PERMISSIONS TO TABLE       
    IF @type = 'U'
      BEGIN    
      --EXECUTE ('GRANT SELECT ON ' + @OWNER + '.' + @name + ' TO pl_user') 
      EXECUTE ('GRANT SELECT, INSERT, UPDATE, DELETE ON ' + @OWNER + '.' + @name + ' TO ckb_users') 
    END
      ELSE
        --GRANT PERMISSION TO STORE PROCEDURE
        IF @type = 'P'
        BEGIN    
          EXECUTE ('GRANT EXEC ON ' + @OWNER + '.' + @name + ' TO ckb_users') 
        END
  END
  FETCH NEXT FROM Tables_Cursor INTO @name, @type
END
CLOSE Tables_Cursor
DEALLOCATE Tables_Cursor
GO
REVOKE INSERT, UPDATE, DELETE ON GIS.PCAT_PERMISSION FROM ckb_users;
GO