Get Model Roles by Model

Applicability:

SQL Server 2000: Not Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Sorna Kumar Muthuraj

Date: 9 Jul 2011

Description

Retrieves a list of roles against one or many models.

NOTE: It is assumed that the Report Server Database has its default name of 'ReportServer'

Code

Stored Procedure:

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, '') + '%'