[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 735
  • Last Modified:

Create list of all reports a user has access to in SSRS

Can a sql query be created that provides a list of all the reports a user has access to in SSRS? I'm looking for the users that are specified in the "Security" tab of each report in the SSRS database. I'm not concerned with users within a group.  We are migrating from SQL 2005 to SQL 2008 R2 and would like to notify the users of each report to take a look at their reports to ensure they are still functioning properly.

Thanks in advance for any help you can provide.

Curt
0
cbroch
Asked:
cbroch
1 Solution
 
Nico BontenbalCommented:
Try to run this query on your reportserver database:
 
SELECT
    Users.UserName, 
    Catalog.Path, 
    Catalog.Name, 
    Catalog.Type
FROM         
    Catalog 
    INNER JOIN Policies ON Catalog.PolicyID = Policies.PolicyID 
    INNER JOIN PolicyUserRole ON Policies.PolicyID = PolicyUserRole.PolicyID 
    INNER JOIN Users ON PolicyUserRole.UserID = Users.UserID
WHERE
    Catalog.Type = 2

Open in new window

0
 
cbrochAuthor Commented:
Nicobo,

That is a thing of beauty! Thank you very much.

Curt
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now