Get Model Roles by Model

DROP PROC usp_GetModelRolesByUser

GO

GO

CREATE PROC usp_GetModelRolesByUser @ModelName NVARCHAR(850) = NULL

AS

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

Purpose: To retrieve a list of roles against one or

many models.

Uses the Catalog, and ModelItemPolicy, PolicyUser,

Role and PolicyUserRoles 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 ModelID,

c.name AS ModelName,

c.path AS ModelFolder,

u.username AS Username,

r.rolename AS RoleName

FROM Catalog c

JOIN modelitempolicy mip (NOLOCK)

ON c.itemid = mip.catalogitemid

JOIN ReportServer.dbo.Policies p (NOLOCK)

ON p.policyid = mip.policyid

JOIN ReportServer.dbo.PolicyUserRole pur (NOLOCK)

ON p.policyid = pur.policyid

JOIN ReportServer.dbo.Users u (NOLOCK)

ON u.userid = pur.userid

JOIN ReportServer.dbo.Roles r (NOLOCK)

ON r.roleid = pur.roleid

-- no restriction on CatalogType added as model policy is just for models

WHERE c.Name LIKE '%' + ISNULL(@ModelName, '') + '%'