Reporting on privilege assignment

Version 1

    An Access SQL query I use to help report and diagnose access privilege issues.  Assumes an ODBC connection to your SQL VCDB called VCDB.

     

    The inner joins are to compensate for the fact that read-only is a reserved role with no VPX_ROLE record and no VPX_PRIV_ROLE entries.

     

    SELECT dbo_VPX_ACCESS.PRINCIPAL, dbo_VPX_OBJECT_TYPE.NAME, dbo_VPX_ENTITY.NAME, dbo_VPX_ROLE.NAME, dbo_VPX_PRIV_ROLE.PRIV_NAME, d bo_VPX_ACCESS.FLAG

    FROM ((dbo_VPX_ACCESS INNER JOIN dbo_VPX_ENTITY ON dbo_VPX_ACCESS.ENTITY_ID = dbo_VPX_ENTITY.ID) LEFT JOIN (dbo_VPX_PRIV_ROLE LEFT JOIN dbo_VPX_ROLE ON dbo_VPX_PRIV_ROLE.ROLE_ID = dbo_VPX_ROLE.ID) ON dbo_VPX_ACCESS.ROLE_ID = dbo_VPX_PRIV_ROLE.ROLE_ID) INNER JOIN dbo_VPX_OBJECT_TYPE ON dbo_VPX_ENTITY.TYPE_ID = dbo_VPX_OBJECT_TYPE.ID

    WHERE (((dbo_VPX_ACCESS.PRINCIPAL)="INTERNAL\test.vm.admins"))

    ORDER BY dbo_VPX_ACCESS.PRINCIPAL, dbo_VPX_OBJECT_TYPE.NAME, dbo_VPX_ENTITY.NAME, dbo_VPX_ROLE.NAME, dbo_VPX_PRIV_ROLE.PRIV_NAME;