Tuesday, May 12, 2009

SSRS 2008 List All Reports, Users, Roles, and Security

Someone on the newsgroups today asked the following question:

Is there any report that can be run in Reporting Services that lists
all reports and the users that have access?

I assumed he meant that he wanted a list of all the reports he has, and a list or roles and the users that are in them. I quickly wrote the following query for him. I hope some of you find it useful:

SELECT u.UserName, c.[Path], c.[Name], r.RoleName, *
FROM Users u
JOIN PolicyUserRole pur
ON u.UserID = pur.UserID
JOIN Policies p
ON pur.policyID = p.PolicyID
JOIN Catalog c
ON p.policyID = c.policyID
JOIN Roles r
ON pur.RoleID = r.RoleID
ORDER BY u.UserName, c.[Name], r.RoleName

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.