report on report security

Question

The xmldescription column in the SecData table in SSRS database contains the security (user access and role ) information for each object in SSRS. Linked back to the catalog table and you can get a complete listing of who has access to what.

We'd like to be able to report on that information however we do not know how to parse the XML in the xmldescription column.

Does anyone know of a script or SSRS sample report that can report on that information? We need to be able to list the users and the roles they have for each object without all the XML tags.

Answer 1

Hi:

This is as far as I got:

CREATE TABLE #tblTextToXml (XmlDescription xml)

INSERT INTO #tblTextToXml
SELECT XmlDescription
FROM SecData

SELECT XmlDescription.query('/Policies/Policy/GroupUserName') AS GrpNme,
	XmlDescription.query('/Policies/Policy/Roles') AS RlNme
  FROM #tblTextToXml
WHERE XmlDescription.exist('/Policies')  = 1

Yields:
GrpNme    RlNme
<GroupUserName>BUILTIN\Administrators</GroupUserName>    <Roles><Role><Name>System Administrator</Name></Role></Roles>
<GroupUserName>BUILTIN\Administrators</GroupUserName>    <Roles><Role><Name>Content Manager</Name></Role></Roles>

Phil

---

PHuhn

Answer 2

Then:

Function XmlVal(fld as String, tag as String) AS String
	Dim _tag As String = "<" & tag & ">"
	Dim _pos as Integer = InStr(fld, _tag)
	If _pos > -1 Then
		fld = Mid(fld, _pos + Len(_tag) )
		_tag = "</" & tag & ">"
		_pos = InStr(fld, _tag)
		If _pos > -1 Then
			fld = Left(fld, _pos -1)
		End If
	End If
	Return fld
End Function

Call with expression:
=Code.XmlVal(Fields!XmlDescription.Value, "Name")
Phil

---

PHuhn

Answer 3

Hi Florida Guy:

I just found another post per your question.  I just queried XmlDescription and got 4 hits.  One of the moderators pointed the person to the following:
  http://msdn.microsoft.com/en-us/library/reportservice2005.reportingservice2005.getpermissions.aspx

I was also looking yesterday at the various stored procedures per some of the discussions.

Phil

---

PHuhn

Answer 4

Hi,

You should use the Reporting Serivces SOAP API instead of directly querying the report policies from report server database. There is a dedicated method GetPolicies which lists the users and the roles for a pariticular item in your report server.

Access http://msdn.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.getpolicies(SQL.90).aspx for its syntax and information.

The following code example is to demo how to use the method GetPolicies in XML datasource to retieve the users and roles for a particular item in the report server.

Code snippet

<Query>
  <Method Name="GetPolicies" Namespace="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices">
     <Parameters>
       <Parameter Name="Item">
        <DefaultValue>/MyFolder/MyReport</DefaultValue>
       </Parameter>
       <Parameter Name="InheritParent">
        <DefaultValue>True</DefaultValue>
       </Parameter>
     </Parameters>
   </Method>
   <ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>

Note: the above will return 4 fields, in them, the GroupUserName field for the users or groups and the Name field for the roles.

In addition, you can refer to this thread: http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/e5511325-2bec-40a6-a3ae-2ee9da3783e4 (my reply) on how to retieve all objects in the report server. So far you should be able to list all the users and the roles per each object in the report server.

thanks,
Jerry