SSRS Folder Permissions

Views 1226

If you have SQL Server Reporting Services (SSRS) 2008 setup and you are wanting a report that allows you to keep track of who has access to the different Report Folder Structures and the permissions that they have this query will give you the result set you require.

   1:  select    c.Name
   2:      , c.path
   3:      , r.RoleName
   4:      , u.UserName
   5:      , r.TaskMask
   6:  from
   7:      Catalog c inner join Policies p on c.PolicyID = p.PolicyID
   8:      inner join PolicyUserRole pur on p.PolicyID = pur.PolicyID
   9:      inner join Roles r on pur.RoleID = r.RoleID
  10:      inner join Users u on pur.UserID = u.UserID
  11:  Where c.type = 1
  12:  order by c.path,r.RoleName,u.username

The different values for the Type are as follows.

  1. Folder
  2. Report
  3. Images
  4. Linked Report
  5. Data Source
  6. Model

Use either of the above in your data source and you are away. Happy reporting.

Thanks to Paul at http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/28c55bd7-b32e-42dd-a5f8-91aa7ff2cd67.

If you include the Role.TaskMask in your query this will return you a value like this 0010101001000100 which relates directly to the permissions for the Browser Role. There is no easy way of getting the actual permissions. But as you can see below they correspond to each of the 16 available task permissions.

Warwick

I am a Microsoft Data Platform MVP as well as a Microsoft Certified Master working as the Principal Consultant here at SQL Masters Consulting. When I am not working with the SQL Server Stack I like to get away to the Snow and spend time Snowboarding.

Search
0

Your Cart