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
We read your blog website, share most practical information in blog. Target & Bidder Statements
ReplyDelete