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.
USE [ReportServer] go SELECT u.username, r.rolename, c.NAME, c.path 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, r.rolename, c.NAME
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.