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
