Problem: SSRS Permissions Overview
I had to create a SSRS permissions overview with all the privileges set for each report, folder and other objects in SQL Server Reporting Services.
When you try to google anything like export permissions SSRS you get tons of results where people have issues with the permissions.
I don’t have issues with the permissions I only want to get an SSRS permissions overview.
Just a simple T-SQL script which combins some of the tables which hold the values I needed.
The script below will show all the different objects in SSRS with their path and name with username and the privilege it has.
FROM [dbo].[users] u
INNER JOIN [dbo].[policyuserrole] pur
ON u.userid = pur.userid
INNER JOIN [dbo].[policies] p
ON p.policyid = pur.policyid
INNER JOIN [dbo].[roles] r
ON r.roleid = pur.roleid
INNER JOIN [dbo].[catalog] c
ON c.policyid = p.policyid
ORDER BY u.username,
More info about the ReportServer database can be found here.
The image below shows how the query returns the results
This is a fairly simple T-SQL script but it can help you out.
I always appreciate comments so if you have one feel free to share it.