The following View shows the Reports which have been granted permissions per Security Role.
The following fields use a STUFF function to return lists of Tools for each Security Role:
dp_Roles.Role_Name ,STUFF((SELECT ', ' + R.Report_Name FROM dp_Role_Reports RR JOIN dp_Reports R ON RR.Report_ID = R.Report_ID WHERE RR.Role_ID = dp_Roles.Role_ID FOR XML PATH('')),1,2,'') AS [Reports]
The following clause limits the list of Security Roles to those with Report permissions. Security Roles without any permissions will drop out of the View:
EXISTS(SELECT 1 FROM dp_Role_Reports RR JOIN dp_Reports R ON RR.Report_ID = R.Report_ID WHERE RR.Role_ID = dp_Roles.Role_ID)
It makes sense to order these by name:
dp_Roles.Role_Name
Keywords: Reports, Noun, User, Permission, Security Role.