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

ċ
usp_GetModelRolesByUser.sql
(1k)
Andy Hughes,
Jul 28, 2012, 10:07 AM
Comments