Nested Roles

Database

Nested Roles

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):

SELECT 

A.GRANTEE AS UserName,

B.DATABASENAME,

B.AccessRight

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

INTERSECT

SELECT

Username,

databasename,

AccessRight

FROM DBC.AllRightsV

WHERE  tablename<>'All' AND DATABASENAME='Mktg' AND UserName  like '%core%_batch%user%'