REPLACE MACRO AllUserRights 
   (USERNAME VARCHAR(128) CHARACTER SET LATIN
   ,OPTIONS VARCHAR(100) DEFAULT '' NOT CASESPECIFIC)  
AS (
/* List rights held by the specified user as direct grants or through roles.
   -- Usage
      The 'Options' parameter controls the display of some rows.
      - this is a delimited set of values, I suggest that you use a space (' ') or a comma (',')
      - the values are: PUB - display AccessRights on the named database granted to PUBLIC
                        SELF - display AccessRights the named database has on itself
                        DBC - display AccessRights that user DBC has on the database
                  (by default none of these sets of AccessRights are displayed)

      The column 'Grant Type' now describes how the user has received the AccessRight:
      - User: the AccessRight was specifically granted to the user
      - Direct Role: the AccessRight was granted to a role and that role was granted to the user
      - Nested Role: the AccessRight was granted to a role which was granted to another role that was granted to the user
      - Public: the AccessRight was granted to 'Public' (by default these AccessRights are not shown, see 'OPTIONS')

   -- Change Log
      - 2015-07-28 Initial coding, based on existing AllDatabaseRightsV macro but coded directly
                   against DBC tables for efficiency.

   -- Notices
      - Developed and maintained by Ward Analytics Ltd 2015
      - You are free to use and redistribute this code but please leave this header here.
*/ 
LOCKING ROW FOR ACCESS 
SELECT CAST(DT1.TGTUSER AS CHAR(30)) AS USERNAME
      ,DT1.AR_TYPE (VARCHAR(11)) (NAMED GRANTTYPE, TITLE 'Grant Type')
      ,CAST(SUBSTRING(TRANSLATE(DT1.ROLENAME USING UNICODE_TO_LOCALE WITH ERROR) FROM 1 FOR 30) AS CHAR(30)) (NAMED DIRECTROLENAME, TITLE 'Direct RoleName')
      ,CAST(SUBSTRING(TRANSLATE(DT1.ROLENAME2 USING UNICODE_TO_LOCALE WITH ERROR) FROM 1 FOR 30) AS CHAR(30)) (NAMED NESTEDROLENAME, TITLE 'Nested Rolename')
      ,CAST(SUBSTRING(TRANSLATE(DB.DATABASENAME USING UNICODE_TO_LOCALE WITH ERROR) FROM 1 FOR 30) AS CHAR(30))
      ,CAST(SUBSTRING(TRANSLATE(TBL.TVMNAME USING UNICODE_TO_LOCALE WITH ERROR) FROM 1 FOR 30) AS CHAR(30)) (NAMED TABLENAME, TITLE 'TableName')
      ,CAST(SUBSTRING(TRANSLATE(FLD.FIELDNAME USING UNICODE_TO_LOCALE WITH ERROR) FROM 1 FOR 30) AS CHAR(30)) (NAMED COLUMNNAME, TITLE 'ColumnName')
      ,DT1.ACCESSRIGHT
      ,CASE
       WHEN DT1.ACCESSRIGHT = 'AE' THEN 'ALTER EXTERNAL PROCEDURE'
       WHEN DT1.ACCESSRIGHT = 'AF' THEN 'ALTER FUNCTION'
       WHEN DT1.ACCESSRIGHT = 'AP' THEN 'ALTER PROCEDURE'
       WHEN DT1.ACCESSRIGHT = 'AR' THEN 'OVERRIDE RESTORE'
       WHEN DT1.ACCESSRIGHT = 'AS' THEN 'ABORTSESSION'
       WHEN DT1.ACCESSRIGHT = 'CA' THEN 'CREATE AUTHORIZATION'
       WHEN DT1.ACCESSRIGHT = 'CD' THEN 'CREATE DATABASE'
       WHEN DT1.ACCESSRIGHT = 'CE' THEN 'CREATE EXTERNAL PROCEDURE'
       WHEN DT1.ACCESSRIGHT = 'CF' THEN 'CREATE FUNCTION'
       WHEN DT1.ACCESSRIGHT = 'CG' THEN 'CREATE TRIGGER'
       WHEN DT1.ACCESSRIGHT = 'CM' THEN 'CREATE MACRO'
       WHEN DT1.ACCESSRIGHT = 'CO' THEN 'CREATE PROFILE'
       WHEN DT1.ACCESSRIGHT = 'CP' THEN 'CHECKPOINT'
       WHEN DT1.ACCESSRIGHT = 'CR' THEN 'CREATE ROLE'
       WHEN DT1.ACCESSRIGHT = 'CT' THEN 'CREATE TABLE'
       WHEN DT1.ACCESSRIGHT = 'CU' THEN 'CREATE USER'
       WHEN DT1.ACCESSRIGHT = 'CV' THEN 'CREATE VIEW'
       WHEN DT1.ACCESSRIGHT = 'CZ' THEN 'CREATE ZONE' /* Added for TD 15.00 */
       WHEN DT1.ACCESSRIGHT = 'D ' THEN 'DELETE'
       WHEN DT1.ACCESSRIGHT = 'DA' THEN 'DROP AUTHORIZATION'
       WHEN DT1.ACCESSRIGHT = 'DD' THEN 'DROP DATABASE'
       WHEN DT1.ACCESSRIGHT = 'DF' THEN 'DROP FUNCTION'
       WHEN DT1.ACCESSRIGHT = 'DG' THEN 'DROP TRIGGER'
       WHEN DT1.ACCESSRIGHT = 'DM' THEN 'DROP MACRO'
       WHEN DT1.ACCESSRIGHT = 'DO' THEN 'DROP PROFILE'
       WHEN DT1.ACCESSRIGHT = 'DP' THEN 'DUMP'
       WHEN DT1.ACCESSRIGHT = 'DR' THEN 'DROP ROLE'
       WHEN DT1.ACCESSRIGHT = 'DT' THEN 'DROP TABLE'
       WHEN DT1.ACCESSRIGHT = 'DU' THEN 'DROP USER'
       WHEN DT1.ACCESSRIGHT = 'DV' THEN 'DROP VIEW'
       WHEN DT1.ACCESSRIGHT = 'DZ' THEN 'DROP ZONE' /* Added for TD 15.00 */
       WHEN DT1.ACCESSRIGHT = 'E ' THEN 'EXECUTE'
       WHEN DT1.ACCESSRIGHT = 'EF' THEN 'EXECUTE FUNCTION'
       WHEN DT1.ACCESSRIGHT = 'GC' THEN 'CREATE GLOP'
       WHEN DT1.ACCESSRIGHT = 'GD' THEN 'DROP GLOP'
       WHEN DT1.ACCESSRIGHT = 'GM' THEN 'GLOP MEMBER'
       WHEN DT1.ACCESSRIGHT = 'I' THEN 'INSERT'
       WHEN DT1.ACCESSRIGHT = 'IX' THEN 'INDEX'
       WHEN DT1.ACCESSRIGHT = 'MR' THEN 'MONRESOURCE'
       WHEN DT1.ACCESSRIGHT = 'MS' THEN 'MONSESSION'
       WHEN DT1.ACCESSRIGHT = 'NT' THEN 'NONTEMPORAL'
       WHEN DT1.ACCESSRIGHT = 'OA' THEN 'OVERRIDE DUMP CONSTRAINT'
       WHEN DT1.ACCESSRIGHT = 'OD' THEN 'OVERRIDE DELETE CONSTRAINT'
       WHEN DT1.ACCESSRIGHT = 'OI' THEN 'OVERRIDE INSERT CONSTRAINT'
       WHEN DT1.ACCESSRIGHT = 'OP' THEN 'CREATE OWNER PROCEDURE'
       WHEN DT1.ACCESSRIGHT = 'OS' THEN 'OVERRIDE SELECT CONSTRAINT'
       WHEN DT1.ACCESSRIGHT = 'OU' THEN 'OVERRIDE UPDATE CONSTRAINT'
       WHEN DT1.ACCESSRIGHT = 'PC' THEN 'CREATE PROCEDURE'
       WHEN DT1.ACCESSRIGHT = 'PD' THEN 'DROP PROCEDURE'
       WHEN DT1.ACCESSRIGHT = 'PE' THEN 'EXECUTE PROCEDURE'
       WHEN DT1.ACCESSRIGHT = 'R' THEN 'SELECT'
       WHEN DT1.ACCESSRIGHT = 'RF' THEN 'REFERENCE'
       WHEN DT1.ACCESSRIGHT = 'RO' THEN 'REPLCONTROL'
       WHEN DT1.ACCESSRIGHT = 'RS' THEN 'RESTORE'
       WHEN DT1.ACCESSRIGHT = 'SA' THEN 'CONSTRAINT ASSIGNMENT'
       WHEN DT1.ACCESSRIGHT = 'SD' THEN 'CONSTRAINT DEFINITION'
       WHEN DT1.ACCESSRIGHT = 'SH' THEN 'SHOW'
       WHEN DT1.ACCESSRIGHT = 'SR' THEN 'SETRESRATE'
       WHEN DT1.ACCESSRIGHT = 'SS' THEN 'SETSESSRATE'
       WHEN DT1.ACCESSRIGHT = 'ST' THEN 'STATISTICS'
       WHEN DT1.ACCESSRIGHT = 'TH' THEN 'CTCONTROL'
       WHEN DT1.ACCESSRIGHT = 'U' THEN 'UPDATE'
       WHEN DT1.ACCESSRIGHT = 'UM' THEN 'UDT METHOD'
       WHEN DT1.ACCESSRIGHT = 'UT' THEN 'UDT TYPE'
       WHEN DT1.ACCESSRIGHT = 'UU' THEN 'UDT USAGE'
       WHEN DT1.ACCESSRIGHT = 'ZO' THEN 'ZONE OVERRIDE' /* Added for TD 15.00 */
       ELSE DT1.ACCESSRIGHT
       END (VARCHAR(26))  (NAMED ACCESSRIGHTDESC, TITLE 'AccessRight Description')
      ,DT1.WITHGRANT (NAMED GRANTAUTHORITY, TITLE 'GrantAuthority')
      ,CAST(SUBSTRING(TRANSLATE(COALESCE(GRANTORDB.DATABASENAME,'Dropped User') USING UNICODE_TO_LOCALE WITH ERROR) FROM 1 FOR 30) AS CHAR(30)) (NAMED GRANTORNAME, TITLE 'GrantorName')
      ,DT1.ALLNESSFLAG
      ,CAST(SUBSTRING(TRANSLATE(COALESCE(CREATORDB.DATABASENAME,'Dropped User') USING UNICODE_TO_LOCALE WITH ERROR) FROM 1 FOR 30) AS CHAR(30)) (NAMED CREATORNAME, TITLE 'CreatorName')
      ,DT1.CREATETIMESTAMP
FROM (/* Direct grants to the user */
      SELECT :USERNAME AS TGTUSER
            ,CAST( (CASE
                    WHEN UDB.DATABASENAME = :USERNAME THEN 'User' 
                    ELSE 'Public'
                    END) AS VARCHAR(11)) AS AR_TYPE
            ,CAST(NULL AS VARCHAR(128)) AS ROLENAME
            ,CAST(NULL AS VARCHAR(128)) AS ROLENAME2
            ,AR1.DATABASEID
            ,AR1.TVMID
            ,AR1.FIELDID
            ,AR1.ACCESSRIGHT
            ,AR1.GRANTORID
            ,AR1.CREATETIMESTAMP
            ,AR1.WITHGRANT
            ,AR1.ALLNESSFLAG
            ,AR1.CREATEUID
      FROM DBC.ACCESSRIGHTS AS AR1
      INNER JOIN DBC.DBASE AS UDB
         ON UDB.DATABASEID = AR1.USERID
          AND (UDB.DATABASENAMEI = :USERNAME (UPPERCASE)
               OR (UDB.DATABASENAMEI = 'PUBLIC' AND POSITION('PUB' IN :OPTIONS) > 0) ) -- control AR's granted to PUBLIC
          AND (AR1.USERID <> AR1.DATABASEID 
               OR (AR1.USERID = AR1.DATABASEID AND POSITION('SELF' IN :OPTIONS) > 0)) -- control AR's on self
          AND (AR1.DATABASEID <> '00000100'XB
               OR (AR1.DATABASEID = '00000100'XB AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC
      UNION ALL
      /* Grants to the user's direct roles */
      SELECT :USERNAME AS TGTUSER
            ,CAST('Direct Role' AS VARCHAR(11)) AS AR_TYPE
            ,R.ROLENAME
            ,CAST(NULL AS VARCHAR(128)) AS ROLENAME2
            ,AR2.DATABASEID
            ,AR2.TVMID
            ,AR2.FIELDID
            ,AR2.ACCESSRIGHT
            ,AR2.GRANTORID
            ,AR2.CREATETIMESTAMP
            ,AR2.WITHGRANT
            ,CAST(NULL AS CHAR(1)) AS ALLNESSFLAG
            ,AR2.CREATEUID
      FROM DBC.ROLEGRANTS AS RG
      INNER JOIN DBC.DBASE AS UDB
         ON UDB.DATABASEID = RG.GRANTEEID
          AND UDB.DATABASENAMEI = :USERNAME (UPPERCASE)
      INNER JOIN DBC.ROLES AS R
         ON RG.ROLEID = R.ROLEID
      INNER JOIN DBC.ACCESSRIGHTS AS AR2
         ON R.ROLEID = AR2.USERID
          AND (AR2.DATABASEID <> '00000100'XB
               OR (AR2.DATABASEID = '00000100'XB AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC
       WHERE (UDB.DATABASEID <> AR2.DATABASEID 
              OR (UDB.DATABASEID = AR2.DATABASEID AND POSITION('SELF' IN :OPTIONS) > 0)) -- control AR's on self
      UNION ALL
      /* Grants to the user's nested roles */
      SELECT :USERNAME AS TGTUSER
            ,CAST('Nested Role' AS VARCHAR(11)) AS AR_TYPE
            ,DR.ROLENAME AS DIRECT_ROLE
            ,NR.ROLENAME AS NESTED_ROLE
            ,AR3.DATABASEID
            ,AR3.TVMID
            ,AR3.FIELDID
            ,AR3.ACCESSRIGHT
            ,AR3.GRANTORID
            ,AR3.CREATETIMESTAMP
            ,AR3.WITHGRANT
            ,CAST(NULL AS CHAR(1)) AS ALLNESSFLAG
            ,AR3.CREATEUID
      FROM DBC.DBASE AS UDB
      INNER JOIN DBC.ROLEGRANTS AS DRG
         ON UDB.DATABASENAMEI = :USERNAME (UPPERCASE)
          AND UDB.DATABASEID = DRG.GRANTEEID
          AND DRG.GRANTEEKIND = 'U'
      INNER JOIN DBC.ROLES AS DR
         ON DRG.ROLEID = DR.ROLEID
      INNER JOIN DBC.ROLEGRANTS AS NRG
         ON DRG.ROLEID = NRG.GRANTEEID
          AND NRG.GRANTEEKIND = 'R'
      INNER JOIN DBC.ROLES AS NR
         ON NRG.ROLEID = NR.ROLEID
      INNER JOIN DBC.ACCESSRIGHTS AS AR3
         ON NR.ROLEID = AR3.USERID
          AND (AR3.DATABASEID <> '00000100'XB
               OR (AR3.DATABASEID = '00000100'XB AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC
      WHERE (UDB.DATABASEID <> AR3.DATABASEID 
             OR (UDB.DATABASEID = AR3.DATABASEID AND POSITION('SELF' IN :OPTIONS) > 0)) -- control AR's on self
     ) AS DT1
INNER JOIN DBC.DBASE AS DB
  ON DT1.DATABASEID = DB.DATABASEID
INNER JOIN DBC.TVM AS TBL
  ON DT1.TVMID = TBL.TVMID
INNER JOIN DBC.TVFIELDS AS FLD
  ON DT1.FIELDID = FLD.FIELDID
   AND (DT1.TVMID = FLD.TABLEID
         OR FLD.FIELDID = 0)
LEFT OUTER JOIN DBC.DBASE AS GRANTORDB
  ON DT1.GRANTORID = GRANTORDB.DATABASEID
LEFT OUTER JOIN DBC.DBASE AS CREATORDB
  ON DT1.CREATEUID = CREATORDB.DATABASEID
ORDER BY 5,6,7,8,2;
);