Tuesday, 13 November 2018

How to find user roles on different Catalog|Folder|Report in SSRS

How to find user roles on different Catalog|Folder|Report in SSRS The below query will give you, the user roles on different catalogs

USE ReportServer
GO
SELECT
    CASE
        WHEN [Catalog].[Type]=1 THEN 'Folder'
        WHEN [Catalog].[Type]=2 THEN 'Report'
        WHEN [Catalog].[Type]=3 THEN 'Resource'
        WHEN [Catalog].[Type]=4 THEN 'Linked Report'
        WHEN [Catalog].[Type]=5 THEN 'Data Source'
        WHEN [Catalog].[Type]=6 THEN 'Report Model'
        WHEN [Catalog].[Type]=8 THEN 'Shared Dataset'
        WHEN [Catalog].[Type]=9 THEN 'Report Part'     
    END AS CatalogType
    ,[Catalog].Name CatalogName
    , Roles.RoleName Role
    , Users.UserName UserId
FROM PolicyUserRole
INNER JOIN Roles ON PolicyUserRole.RoleID = Roles.RoleID
INNER JOIN Policies ON PolicyUserRole.PolicyID = Policies.PolicyID
INNER JOIN Users ON PolicyUserRole.UserID = Users.UserID
INNER JOIN [Catalog] ON PolicyUserRole.PolicyID = [Catalog].PolicyID

Order by Users.UserName