Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Auditing user permissons in SSRS (For each client folder) Expand / Collapse
Author
Message
Posted Friday, September 3, 2010 3:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 23, 2014 9:25 AM
Points: 184, Visits: 852
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


Sanz
Post #980512
Posted Tuesday, September 7, 2010 2:11 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 5:49 AM
Points: 272, Visits: 496
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




http://sqlreportingservicescrystalreports.blogspot.com


Post #981391
Posted Tuesday, September 7, 2010 4:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 23, 2014 9:25 AM
Points: 184, Visits: 852
Exactly what I needed. Thanks a ton !

Sanz
Post #981431
Posted Tuesday, June 19, 2012 1:25 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:25 PM
Points: 258, Visits: 661
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.
Post #1318282
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse