Reports and SSRS - Get Report Roles by Report

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 the roles for each user against one or many reports and/or folders.

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

Code

Stored Procedure:


DROP PROC usp_GetReportRolesByUser
GO

CREATE PROC usp_GetReportRolesByUser @ItemName NVARCHAR(850) = NULL
AS

/****************************************************
Purpose:   To retrieve a list of the roles for each user against one or
           many reports and/or folders.
          
           Uses the Catalog, Policies, Roles and Users 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 ItemID,
  
CASE c.TYPE
       WHEN
1 THEN 'Folder'
      
WHEN 2 THEN 'Report'
  
END                             AS ItemType,
  
c.name                          AS ItemName,
  
c.Path                          AS ItemFolder,
    
u.username                     AS UserName,
    
u.authtype                     AS AuthType,
  
    
-- these values are not 100% validated with MS
   --CASE u.authtype
   -- WHEN 0 THEN 'WindowsUser'
   -- WHEN 1 THEN 'WindowsGroup'
   -- WHEN 2 THEN 'SqlLogin'
   -- WHEN 3 THEN 'Certificate'
   -- WHEN 4 THEN 'AsymmetricKey'
   --END AS AuthType,
  
    
r.rolename                     AS RoleName,
    
r.DESCRIPTION                  AS RoleDescription
FROM ReportServer.dbo.users u (NOLOCK)
INNER JOIN ReportServer.dbo.policyuserrole pur (NOLOCK)
  
ON u.userid = pur.userid
INNER JOIN ReportServer.dbo.policies p (NOLOCK)
  
ON p.policyid = pur.policyid
INNER JOIN ReportServer.dbo.roles r (NOLOCK)
  
ON r.roleid = pur.roleid
INNER JOIN ReportServer.dbo.catalog c (NOLOCK)
  
ON c.policyid = p.policyid
WHERE c.TYPE IN (1,2) -- For Reports and Folders Only
AND c.Name LIKE '%' + ISNULL(@ItemName, '') + '%'
ORDER BY
  
c.TYPE,
  
c.name,
  
u.username

ċ
usp_GetReportRolesByUser.sql
(2k)
Andy Hughes,
Jul 28, 2012, 9:59 AM
Comments