asked on

SQL Query to get Folder structure out of Report Manager

Hi,

I'm using SQLServer 2008 R2 and I'm trying to create a report to help me managing the security for all my groups and users across all my folders and reports. I want to see users/groups for the parent folder and what security is allocated to each one, and then see the same for all the subfolders and finally the reports.

In the 'Catalog' table I have a path like ParentFolder/subfolder1/subfolder2/subfolder3/report1 or similar for each item.
Is there a field in any table that can help me to identify the level- sub level of the each folders?

Alternatively, does anyone know how to get an overview of the security allocated to each object (folder, subfolders, reports...) in Report Manager?

Thanks

ValentinoV

"Alternatively, does anyone know how to get an overview of the security allocated to each object (folder, subfolders, reports...) in Report Manager?"

I think you'll find the following an interesting query (to be run on the ReportServer database):

SELECT C.Name, U.UserName, R.RoleName, R.Description, U.AuthType 
      , case C.[Type]
        when 1 then 'Folder'
        when 2 then 'Report'
        when 3 then 'Resource'
        when 4 then 'Linked Report'
        when 5 then 'Data Source'
        when 6 then 'Report Model'
        when 7 then '???'
        when 8 then 'Shared Dataset'
        when 9 then 'Report Part'
    end ObjectType
FROM Reportserver.dbo.Users U 
JOIN Reportserver.dbo.PolicyUserRole PUR ON U.UserID = PUR.UserID 
JOIN Reportserver.dbo.Policies P ON P.PolicyID = PUR.PolicyID 
JOIN Reportserver.dbo.Roles R ON R.RoleID = PUR.RoleID 
JOIN Reportserver.dbo.Catalog C ON C.PolicyID = P.PolicyID

Open in new window

The JOINs are based on a query which I found here: Query Details of given SSRS Report from ReportServer Database tables

And the Catalog.Type logic comes from one of my articles (which possibly contains other queries you might be interested in if you want to understand the ReportServer tables better): SSRS Deployment: Generate The Batch Script Through SQL!

ASKER CERTIFIED SOLUTION

ValentinoV

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

monserob

ASKER

That first query es very similar to what I was working on, but still misses the structure needed to create a proper report in SSRS, giving me the users and security granted for each folder and report

The code about the path helps to get the number of levels in the path string, but is not 100% applicable as some folders have a name including a "/" such us "budget for 2012/2013". I will have to work around this problem.

Thanks

ValentinoV

Hi monserob,

I noticed that you closed this question with a C-grade. I think that's a little undeserved. I believe I've provided you with useful information. Also, in general experts and askers interact in order to come to a satisfying result for both parties. Is there any reason why you didn't do that here?

Please see the guidelines on grading here: http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-?b_id=44

"That first query es very similar to what I was working on, but still misses the structure needed to create a proper report in SSRS, giving me the users and security granted for each folder and report "

I don't see what is missing? The query gives you a list of users and the report names to which they have access. And the RoleName column shows the access level, just as you can see it in the Security page of the Report Manager. Furthermore, the ObjectType column allows you to group the different object types (folders, reports, ...) if needed.

"The code about the path helps to get the number of levels in the path string, but is not 100% applicable as some folders have a name including a "/" such us "budget for 2012/2013". I will have to work around this problem."

Well, yes. But I couldn't have known that, right? See how the interaction part can be interesting?

Valentino.