To retrieve a list of all reports on a Microsoft SQL Server Reporting Services (SSRS) instance along with the security principals that have permissions on each report, you can query the SSRS database. SSRS uses a database (commonly named ReportServer) to store metadata about reports and their security settings.
Use the following query to get the list of reports and their associated security principals:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
USE ReportServer; SELECT c.Name AS ReportName, c.Path AS ReportPath, u.UserName AS SecurityPrincipal, r.RoleName AS Role FROM Catalog c JOIN PolicyUserRole pur ON c.PolicyID = pur.PolicyID JOIN Users u ON pur.UserID = u.UserID JOIN Roles r ON pur.RoleID = r.RoleID WHERE c.Type = 2 -- Type 2 indicates a report ORDER BY c.Path, SecurityPrincipal; |
This will result in the following table:
Good luck!
