SSRS Folder Permissions

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.