Get Model Roles by Model


SQL Server 2000: Not Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested


Author: Sorna Kumar Muthuraj

Date: 9 Jul 2011


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'


Stored Procedure:

DROP PROC usp_GetModelRolesByUser



CREATE PROC usp_GetModelRolesByUser @ModelName NVARCHAR(850) = NULL



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



c.itemid AS ModelID, 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, '') + '%'