How to get the folder wise security info of all the users?

Hi,

The Report Manager portal has many folders. For each folder there are specific users with different roles.

I am trying to figure out the way to extract User, folder wise security data. I want to run a query and retrieve users name, the folders they have access to and the user role corresponding to that folder.

Use ReportServer

SELECT u.UserName, r.RoleName FROM users u, policyuserrole pur, roles r

WHERE pur.UserID=u.UserID AND pur.RoleID=r.RoleID

The above query fetches all the users and their roles.

The folder information corresponds to Path column of Catalog table. Am unable link this table with the above query.

TIA

The security settings appear to be in XML. See the GetPolicy stored procedure.

select xmldescription from secdata

You can see the security on each folder by running rsscripter utility and looking at the resulting code in the fldr.rss files.

'Set Folder security
Dim pol(4) As [Policy]
Dim sersec As Serialization.XmlSerializer = New Serialization.XmlSerializer(GetType(Policy))
Dim tsec0 As New StringReader("<?xml version=""1.0"" encoding=""utf-16""?><Policy xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""> <GroupUserName xmlns=""http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"">BUILTIN\Administrators</GroupUserName> <Roles xmlns=""http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices""> <Role> <Name>Content Manager</Name> </Role> </Roles></Policy>")

If I was trying to reverse-engineer how to get the security roles, I would look into SQL Profiler to see what calls are being made when setting/getting security on folders.

You may have more luck using SOAP & Web Service to get the information you want.

regards,

Andrew