SSRS -How To Get List of Folders,DataSources,Reports and Permissions on Each of Item in SQL Server Reporting Services

The below code can be used to get the list of Folders, Items in them such as reports, data sources and permission to each of these items in SQL Server Reporting Services. The code uses ReportServerDB tables to provide us all details.


USE ReportServer
GO
SELECT CASE
         WHEN C.Path = '' THEN 'Home'
         ELSE C.Path
       END    AS Path,
       C.Name AS ItemName,
       USR.UserName,
       RL.RoleName,
       CASE
         WHEN C.TYPE = 1 THEN 'Folder'
         WHEN C.TYPE = 2 THEN 'Report'
         WHEN C.TYPE = 3 THEN 'File'
         WHEN C.TYPE = 4 THEN 'LinkedReport'
         WHEN C.TYPE = 5 THEN 'DataSource'
         WHEN C.TYPE = 6 THEN 'Model'
         WHEN C.TYPE = 7 THEN 'ReportPart'
         WHEN C.TYPE = 8 THEN 'SharedDataset'
       END    AS ItemType FROM   Catalog C
       INNER JOIN Policies PL
               ON C.PolicyID = PL.PolicyID
       INNER JOIN PolicyUserRole PUR
               ON PUR.PolicyID = PL.PolicyID
       INNER JOIN Users USR
               ON PUR.UserID = USR.UserID
       INNER JOIN dbo.Roles RL
               ON RL.RoleID = PUR.RoleID
ORDER  BY C.Path 

1 comment: