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
cbrochAsked:
Who is Participating?
 
Nico BontenbalConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.