SELECT
x.ROLENAME,
x.AccessRight,
x.DATABASENAME,
x.TABLENAME,
'GRANT '|| CASE
WHEN ACCESSRIGHT = 'AE' THEN 'ALTER EXTERNAL PROCEDURE'
WHEN ACCESSRIGHT = 'AF' THEN 'ALTER FUNCTION'
WHEN ACCESSRIGHT = 'AP' THEN 'ALTER PROCEDURE'
WHEN ACCESSRIGHT = 'AR' THEN 'OVERRIDE RESTORE'
WHEN ACCESSRIGHT = 'AS' THEN 'ABORT SESSION'
WHEN ACCESSRIGHT = 'CA' THEN 'CREATE AUTHORIZATION'
WHEN ACCESSRIGHT = 'CD' THEN 'CREATE DATABASE'
WHEN ACCESSRIGHT = 'CE' THEN 'CREATE EXTERNAL PROCEDURE'
WHEN ACCESSRIGHT = 'CF' THEN 'CREATE FUNCTION'
WHEN ACCESSRIGHT = 'CG' THEN 'CREATE TRIGGER'
WHEN ACCESSRIGHT = 'CM' THEN 'CREATE MACRO'
WHEN ACCESSRIGHT = 'CO' THEN 'CREATE PROFILE'
WHEN ACCESSRIGHT = 'CP' THEN 'CHECKPOINT'
WHEN ACCESSRIGHT = 'CR' THEN 'CREATE ROLE'
WHEN ACCESSRIGHT = 'CT' THEN 'CREATE TABLE'
WHEN ACCESSRIGHT = 'CU' THEN 'CREATE USER'
WHEN ACCESSRIGHT = 'CV' THEN 'CREATE VIEW'
WHEN ACCESSRIGHT = 'D' THEN 'DELETE'
WHEN ACCESSRIGHT = 'DA' THEN 'DROP AUTHORIZATION'
WHEN ACCESSRIGHT = 'DD' THEN 'DROP DATABASE'
WHEN ACCESSRIGHT = 'DF' THEN 'DROP FUNCTION'
WHEN ACCESSRIGHT = 'DG' THEN 'DROP TRIGGER'
WHEN ACCESSRIGHT = 'DM' THEN 'DROP MACRO'
WHEN ACCESSRIGHT = 'DO' THEN 'DROP PROFILE'
WHEN ACCESSRIGHT = 'DP' THEN 'DUMP'
WHEN ACCESSRIGHT = 'DR' THEN 'DROP ROLE'
WHEN ACCESSRIGHT = 'DT' THEN 'DROP TABLE'
WHEN ACCESSRIGHT = 'DU' THEN 'DROP USER'
WHEN ACCESSRIGHT = 'DV' THEN 'DROP VIEW'
WHEN ACCESSRIGHT = 'E' THEN 'EXECUTE'
WHEN ACCESSRIGHT = 'EF' THEN 'EXECUTE FUNCTION'
WHEN ACCESSRIGHT = 'GC' THEN 'CREATE GLOP'
WHEN ACCESSRIGHT = 'GD' THEN 'DROP GLOP'
WHEN ACCESSRIGHT = 'GM' THEN 'GLOP MEMBER'
WHEN ACCESSRIGHT = 'I' THEN 'INSERT'
WHEN ACCESSRIGHT = 'IX' THEN 'INDEX'
WHEN ACCESSRIGHT = 'MR' THEN 'MONITOR RESOURCE'
WHEN ACCESSRIGHT = 'MS' THEN 'MONITOR SESSION'
WHEN ACCESSRIGHT = 'NT' THEN 'NONTEMPORAL'
WHEN ACCESSRIGHT = 'OA' THEN 'OVERRIDE DUMP'
WHEN ACCESSRIGHT = 'OD' THEN 'OVERRIDE DELETE POLICY'
WHEN ACCESSRIGHT = 'OI' THEN 'OVERRIDE INSERT POLICY'
WHEN ACCESSRIGHT = 'OP' THEN 'CREATE OWNER PROCEDURE'
WHEN ACCESSRIGHT = 'OS' THEN 'OVERRIDE SELECT POLICY'
WHEN ACCESSRIGHT = 'OU' THEN 'OVERRIDE UPDATE POLICY'
WHEN ACCESSRIGHT = 'PC' THEN 'CREATE PROCEDURE'
WHEN ACCESSRIGHT = 'PD' THEN 'DROP PROCEDURE'
WHEN ACCESSRIGHT = 'PE' THEN 'EXECUTE PROCEDURE'
WHEN ACCESSRIGHT = 'R' THEN 'SELECT'
WHEN ACCESSRIGHT = 'RF' THEN 'REFERENCE'
WHEN ACCESSRIGHT = 'RO' THEN 'REPLCONTROL'
WHEN ACCESSRIGHT = 'RS' THEN 'RESTORE'
WHEN ACCESSRIGHT = 'SA' THEN 'SECURITY CONSTRAINT ASSIGNMENT (system wide)'
WHEN ACCESSRIGHT = 'SD' THEN 'SECURITY CONSTRAINT DEFINITION (system wide)'
WHEN ACCESSRIGHT = 'SH' THEN 'SHOW'
WHEN ACCESSRIGHT = 'SR' THEN 'SET RESOURCE RATE'
WHEN ACCESSRIGHT = 'SS' THEN 'SET SESSION RATE'
WHEN ACCESSRIGHT = 'ST' THEN 'STATISTICS'
WHEN ACCESSRIGHT = 'TH' THEN 'CTCONTROL'
WHEN ACCESSRIGHT = 'U' THEN 'UPDATE'
WHEN ACCESSRIGHT = 'UM' THEN 'UDT METHOD'
WHEN ACCESSRIGHT = 'UT' THEN 'UDT TYPE'
WHEN ACCESSRIGHT = 'UU' THEN 'UDT USAGE'
END || ' ON '||TRIM(x.databasename)||' to '||TRIM(x.ROLENAME)||';' AS Permission
FROM
(SELECT distinct A.ROLENAME as ROLENAME,
B.DATABASENAME as DATABASENAME,
B.TABLENAME as TABLENAME,
B.GRANTORNAME as GRANTORNAME,
B.AccessRight as AccessRight
FROM DBC.ROLEMEMBERS A
JOIN DBC.ALLROLERIGHTS B
ON A.ROLENAME = B.ROLENAME
WHERE DATABASENAME =' < database_name > ' --- database name between quotes
-- WHERE B.ROLENAME=' < role_name > ' --- role name between quotes
GROUP BY 1,2,3,4,5) as x
order by 1,2;