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 

2 comments:

  1. We read your blog website, share most practical information in blog. Target & Bidder Statements

    ReplyDelete
  2. It is with great pride that I share with you today the details of Denton Foundation Repair: affordable prices, excellent customer service, and meticulous attention to detail. With the necessary training, experience, and equipment, these professionals can locate the root cause of foundation problems and implement practical fixes. When it comes to contact us the integrity and value of the property, they will make sure that the foundation repair in Denton is stable and consult us. Denton's foundation repair experts provide reliable, long-lasting solutions for any kind of foundation problem using tried-and-true techniques including helical piers and slab piers.

    ReplyDelete