SSRS-Users, Reports and Permissions

Working with SSRS security can be frustrating at times because of the limitations of the web interface, especially if your company has thousands of reports and users.  If you work with SSRS it is important to get familiar with the ReportServer database in SSMS.  All of the data, including the XML that produces reports on the report Server is located in ReportServer tables.

The following is a simple query to return UserName, Roles, Role Descriptions, Report Path and Report Name.  This script will come in handy if you are ever asked to provide a list of users, their roles and what reports they can access.

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

Where C.UserName = 'DOMAIN\USERNAME'

order by C.UserName   

-----------------------------------------------------------------------------