September 3, 2010 at 3:17 pm
Hello All,
I have many reports for various clients in SSRS. Reports for each client is kept under different client folders. User permissions for each client is set under these folders. I need to create a report for auditing purpose to show which users has access which folder.
Which tables in the database can give me this information ?
Thanx in Advance !
Sanz
September 7, 2010 at 2:11 am
Hi,
does this get you started:
To get a list of the top-level folders and associated user permissions:
SELECT Catalog.Name, Catalog.Path, Users.UserName
FROM Catalog INNER JOIN
Policies ON Catalog.PolicyID = Policies.PolicyID INNER JOIN
PolicyUserRole ON PolicyUserRole.PolicyID = Policies.PolicyID INNER JOIN
Users ON PolicyUserRole.UserID = Users.UserID
WHERE (Catalog.ParentID =
(SELECT ItemID
FROM Catalog
WHERE (ParentID IS NULL)))
ORDER BY Catalog.Path, Users.UserName
To get a full breakdown of which users have access rights to reports in Reporting Services:
SELECT Catalog.Path, Catalog.Name, Users.UserName, Catalog.Type
FROM Catalog INNER JOIN
Policies ON Catalog.PolicyID = Policies.PolicyID INNER JOIN
PolicyUserRole ON PolicyUserRole.PolicyID = Policies.PolicyID INNER JOIN
Users ON PolicyUserRole.UserID = Users.UserID
ORDER BY Catalog.Path
September 7, 2010 at 4:07 am
Exactly what I needed. Thanks a ton !
June 19, 2012 at 1:25 pm
What if the folder permissions are handled through active directory? Do you know of any way to get a list of ad users who have permissions to each folder in SSRS? Any help will be greatly appreciated.
July 1, 2015 at 9:40 am
Thank you for this - I have been looking for something for a while and this fits the bill exactly.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply