Auditing user permissons in SSRS (For each client folder)

  • Sanz

    Hall of Fame

    Points: 3157

    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

    [font=Comic Sans MS]Sanz[/font]

  • Dom Horton

    SSCrazy

    Points: 2092

    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

  • Sanz

    Hall of Fame

    Points: 3157

    Exactly what I needed. Thanks a ton !

    [font=Comic Sans MS]Sanz[/font]

  • bpowers

    SSCertifiable

    Points: 5483

    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.

  • rsmart 42717

    SSC Enthusiast

    Points: 113

    Thank you for this - I have been looking for something for a while and this fits the bill exactly.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply