sql-serverssrs-2008

SSRS - Determine report permissions via ReportServer database tables?


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.


Solution

  • 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