Question How can I make a single report that shows all of the security roles and the privileges assigned to each role?
This can be done with the following SQL query:
select sr.name as 'Security Role', sg.nameref as 'Display Group', sp.name as 'Privilege' from securityrole sr join securityprivilegetrustee spt on spt.trusteeguid = sr.trusteeguid Join SecurityPrivilege sp on sp.guid = spt.privilegeguid join SecurityPrivilegeDisplayGroup sg on sg.guid = sp.displaygroupguid where sr.name like '%' order by sr.name, sg.nameref
Note: A report has been created using the query above. Save the attached file in this KB article on your Notification Server and import it under your Reports Tab. This report has the capability of selecting a specific Security Role and show the privileges assigned to it. As well you will be able to see all the Security Roles at once with their proper privileges.