Get Report Roles by Report

DROP PROC usp_GetReportRolesByUser

GO

CREATE PROC usp_GetReportRolesByUser @ItemName NVARCHAR(850) = NULL

AS

/****************************************************

Purpose: To retrieve a list of the roles for each user against one or

many reports and/or folders.

Uses the Catalog, Policies, Roles and Users tables

in the report server database (assumed to be called by its default

name of 'ReportServer')

Author: Sorna Kumar Muthuraj

History: 9 Jul 2011 - Initial Issue

*****************************************************/

SELECT

c.ItemID AS ItemID,

CASE c.TYPE

WHEN 1 THEN 'Folder'

WHEN 2 THEN 'Report'

END AS ItemType,

c.name AS ItemName,

c.Path AS ItemFolder,

u.username AS UserName,

u.authtype AS AuthType,

-- these values are not 100% validated with MS

--CASE u.authtype

-- WHEN 0 THEN 'WindowsUser'

-- WHEN 1 THEN 'WindowsGroup'

-- WHEN 2 THEN 'SqlLogin'

-- WHEN 3 THEN 'Certificate'

-- WHEN 4 THEN 'AsymmetricKey'

--END AS AuthType,

r.rolename AS RoleName,

r.DESCRIPTION AS RoleDescription

FROM ReportServer.dbo.users u (NOLOCK)

INNER JOIN ReportServer.dbo.policyuserrole pur (NOLOCK)

ON u.userid = pur.userid

INNER JOIN ReportServer.dbo.policies p (NOLOCK)

ON p.policyid = pur.policyid

INNER JOIN ReportServer.dbo.roles r (NOLOCK)

ON r.roleid = pur.roleid

INNER JOIN ReportServer.dbo.catalog c (NOLOCK)

ON c.policyid = p.policyid

WHERE c.TYPE IN (1,2) -- For Reports and Folders Only

AND c.Name LIKE '%' + ISNULL(@ItemName, '') + '%'

ORDER BY

c.TYPE,

c.name,

u.username