List all Security roles for particular entity and privilige

I was struggling to figure out which security role has given delete access to particular custom entity. And I was getting bit annoyed by going to each security role and opening it. Finally I written some sql which does this for me.


select * from Privilege inner join PrivilegeObjectTypeCodes on PrivilegeObjectTypeCodes.PrivilegeId = Privilege.PrivilegeId
where ObjectTypeCode = <<entity type code>>e.g. 10056

then look copy value of specific privilege and use it at following query e.g. delete access of custom entity y is 65536

 select * from [role]
inner join RolePrivileges on [role].RoleId = RolePrivileges.RoleId
inner join Privilege on RolePrivileges.PrivilegeId = Privilege.PrivilegeId
inner join PrivilegeObjectTypeCodes on PrivilegeObjectTypeCodes.PrivilegeId = Privilege.PrivilegeId
where ObjectTypeCode = <<entity type code>> and accessright = <<accessright>>


---

--Following query lists the security roles which are currently not in use

select distinct r.Name from [Role] r

where r.name

not in

( select distinct r.Name from SystemUserRoles  sur

 inner join [Role] r on sur.RoleId = r.RoleId

)

 

 

--Following query lists the security roles which are currently in use 

select distinct r.Name from SystemUserRoles  sur

inner join [Role] r on sur.RoleId = r.RoleId

 

--Following query lists down the user and security role assigned to them

select r.Name, su.FullName from SystemUserRoles  sur

inner join [Role] r on sur.RoleId = r.RoleId

inner join SystemUser su on sur.SystemUserId = su.SystemUserId

order by su.FullName,r.Name