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