SSRS - Determine report permissions via ReportServer database tables?

sql-server ssrs-2008
Emre Motan picture Emre Motan · Jul 6, 2011 · Viewed 34k times · Source

How can one programmatically determine logins/users that have permission to access specific SSRS reports?

I want to create a datamart in order to populate reports for managers who want to see who has access to specific reports. We currently assign permissions to Active Directory groups which are then used by SQL Server and SSRS to determine permissions. I want to know if there is a table within SSRS's metadata which tracks how permissions are assigned to reports.

Answer

AndrewBay picture AndrewBay · Jul 7, 2011

This is a script that does most of what you want, you can tweak it to your needs:

select C.UserName, D.RoleName, D.Description, E.Path, E.Name 
from dbo.PolicyUserRole A
   inner join dbo.Policies B on A.PolicyID = B.PolicyID
   inner join dbo.Users C on A.UserID = C.UserID
   inner join dbo.Roles D on A.RoleID = D.RoleID
   inner join dbo.Catalog E on A.PolicyID = E.PolicyID
order by C.UserName

you can run the script on the SSRS SQL ReportServer

Related questions

SSRS Query execution failed for dataset

Have just deployed my Project on to my reporting Server. I have multiple datasets which are referencing views which exist on the db on that server. When I try to go into any report part I am getting this message: …

Read More
Add column to SQL query results

I'm putting together a report in SSRS. The dataset is populated with a SQL query of an MS SQL server. It's querying several similar tables using Union All. The problem is that there's some information loss. The different tables are …

Read More
SSRS the definition of the report is invalid

How can I resolve the below error message that I receive when I try to deploy my report to our our Reporting Server using BIDS? Message: Error : The definition of the report '/OrderAnalysis' is invalid. Report Element: <Report …

Read More