We are evaluting redundant access rights that are already mapped through roles.We have role groups, which are nested roles,these role groups are mapped to corresponding roles, which are assigned to users. To find out the common access rights that are granted through roles and directly on DBC.AllRights , I am using the below query,could you please guide if the below query will provide the desired output, without missing any access rights assigned.I am excluding cross Db access rights and access rights on entire Databases(via dbc.allrights):
A.GRANTEE AS UserName,
FROM DBC.ROLEMEMBERSV A
JOIN DBC.ALLROLERIGHTSV B
ON A.ROLENAME = B.ROLENAME
WHERE GranteeKind='USER' AND TABLENAME='All' AND COLUMNNAME='All' AND B.DATABASENAME='Mktg' AND A.GRANTEE like '%core%_batch%user%'
GROUP BY 1,2,3
WHERE tablename<>'All' AND DATABASENAME='Mktg' AND UserName like '%core%_batch%user%'