Read/Write privileges are already assigned to all the product library tables, except the PL_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 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