Granting privileges to product library components using a script

Available with Production Mapping license.

Since the nautical product library XML was imported into the geodatabase, you need to assign permissions in the new nautical product library geodatabase.

The PL_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:

/*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 PRODLIB.PL_PERMISSION FROM ckb_users;
GO