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
monserobAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ValentinoVBI ConsultantCommented:
"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!
0
ValentinoVBI ConsultantCommented:
"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? "


You could count the number of occurrences of the slash character to get a folder level, like so:

SELECT Path
      , LEN(Path) - LEN(REPLACE(Path, '/', '')) as Level
FROM ReportServer.dbo.Catalog

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
monserobAuthor Commented:
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
0
ValentinoVBI ConsultantCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.