SQL Query to Get Reporting Services Report Permissions SSRS 2012

Sometimes you need a way to quickly get a list of users that have permissions to all of your SQL Server Reporting Services reports. This is especially true if you have fine grained permissions or use Active Directory roles that are constantly changing.

The idea is simple; query the ReportServer database and find who has access to what. The list of reports and their associated security data can be found in the Catalog and SecData tables. The security values are stored as XML in a column called XmlDescription.

Once you join the report and it's associated security values you need to shred the XML using cross apply. Using the XML datatype methods in SQL Server 2012 you can get to a clean list. Here's the reading material – value() Method (xml Data Type)

select Name
       ,perms.value('.', 'varchar(200)') as UserRole
from (select Name
	     ,cast(XmlDescription as xml) as XmlDesc
      from Catalog
	left outer join SecData
	  on Catalog.PolicyID = SecData.PolicyID) shred
cross apply XmlDesc.nodes('/Policies/Policy/GroupUserName') as XmlNodes(perms)

Pretty simple, quick way to audit your report permissions using a SQL query against SSRS ReportServer database.