Query to check what all access given for the databases by User Name.

Query to check what all access given for the databases by User Name.

Step 1:

CREATE VOLATILE SET TABLE AccessRightsAbbv

(

Description VARCHAR(50),

AccessRight CHAR(2)

)PRIMARY INDEX(AccessRight) ON COMMIT PRESERVE ROWS;

ins AccessRightsAbbv('CHECKPOINT','CP');

ins AccessRightsAbbv('CREATE AUTHORIZATION','CA');

ins AccessRightsAbbv('CREATE MACRO','CM');

ins AccessRightsAbbv('CREATE PROCEDURE','PC');

ins AccessRightsAbbv('CREATE TABLE','CT');

ins AccessRightsAbbv('CREATE TRIGGER','CG');

ins AccessRightsAbbv('CREATE VIEW','CV');

ins AccessRightsAbbv('DELETE','D');

ins AccessRightsAbbv('DROP AUTHORIZATION','DA');

ins AccessRightsAbbv('DROP FUNCTION ','DF');

ins AccessRightsAbbv('DROP MACRO','DM');

ins AccessRightsAbbv('DROP PROCEDURE','PD');

ins AccessRightsAbbv('DROP TRIGGER','DG');

ins AccessRightsAbbv('DROP VIEW','DV');

ins AccessRightsAbbv('DUMP','DP');

ins AccessRightsAbbv('INSERT','I');

ins AccessRightsAbbv('RESTORE','RS');

ins AccessRightsAbbv('SELECT','R');

ins AccessRightsAbbv('UPDATE','U');

ins AccessRightsAbbv('CREATE DATABASE','CD');

ins AccessRightsAbbv('CREATE USER','CU');

ins AccessRightsAbbv('DROP DATABASE','DD');

ins AccessRightsAbbv('DROP USER','DU');

ins AccessRightsAbbv('ALTER EXTERNAL PROCEDURE','AE');

ins AccessRightsAbbv('ALTER FUNCTION','AF');

ins AccessRightsAbbv('ALTER PROCEDURE','AP');

ins AccessRightsAbbv('CREATE EXTERNAL PROCEDURE','CE');

ins AccessRightsAbbv('CREATE FUNCTION','CF');

ins AccessRightsAbbv('EXECUTE FUNCTION','EF');

ins AccessRightsAbbv('EXECUTE PROCEDURE','PE');

ins AccessRightsAbbv('EXECUTE','E');

ins AccessRightsAbbv('DROP TABLE','DT');

ins AccessRightsAbbv('INDEX','IX');

ins AccessRightsAbbv('REFERENCES','RF');

ins AccessRightsAbbv('ABORT SESSION','AS');

ins AccessRightsAbbv('MONRESOURCE','MR');

ins AccessRightsAbbv('MONSESSION','MS');

ins AccessRightsAbbv('SETRESRATE','SR');

ins AccessRightsAbbv('SETSESSRATE','SS');

ins AccessRightsAbbv('CREATE PROFILE','CO');

ins AccessRightsAbbv('CREATE ROLE','CR');

ins AccessRightsAbbv('DROP PROFILE','DO');

ins AccessRightsAbbv('DROP ROLE','DR');

ins AccessRightsAbbv('REPLCONTROL','RO');

Step 2:

SELECT

A.ROLENAME

,A.GRANTEE

--,A.GRANTOR

--,A.DefaultRole,A.WithAdmin

,B.DATABASENAME

--,B.TABLENAME,B.COLUMNNAME,B.GRANTORNAME

,B.AccessRight

,CASE   WHEN c.AccessRight IS NOT NULL THEN c.Description

 ELSE b.AccessRight END AS AccessRight

FROM DBC.ROLEMEMBERS A

INNER JOIN DBC.ALLROLERIGHTS B

ON A.ROLENAME = B.ROLENAME

LEFT OUTER JOIN AccessRightsAbbv C

ON B.AccessRight = C.AccessRight

WHERE Grantee in (‘User Name’)

AND B.DATABASENAME IN

(

‘List of DB Names’

)

GROUP BY 1,2,3,4,5--,6,7,8,9,10,11

ORDER BY 2,1,3,4,5;

SELECT

A.ROLENAME

,A.GRANTEE

--,A.GRANTOR

--,A.DefaultRole,A.WithAdmin

,B.DATABASENAME

--,B.TABLENAME,B.COLUMNNAME,B.GRANTORNAME

,B.AccessRight

,CASE   WHEN c.AccessRight IS NOT NULL THEN c.Description

 ELSE b.AccessRight END AS AccessRight

FROM DBC.ROLEMEMBERS A

INNER JOIN DBC.ALLROLERIGHTS B

ON A.ROLENAME = B.ROLENAME

LEFT OUTER JOIN AccessRightsAbbv C

ON B.AccessRight = C.AccessRight

WHERE Grantee in (‘User Name’)

AND B.DATABASENAME IN

(

‘List of DB Names’

)

GROUP BY 1,2,3,4,5--,6,7,8,9,10,11

ORDER BY 2,1,3,4,5;