[SQL] ReportServer Catalog queries

–the following tsql maps out the folder structures, including parent-child relationships
–Use ReportServer;
SELECT
SUBSTRING(c.path,1,CHARINDEX(‘/’,c.path,2)) [Section],
c.Path
, c.Name [ChildName]
, c.Description
, c.Type
, CASE c.TYPE WHEN 1 THEN ‘Folder’
    WHEN 2 THEN ‘Report’
    WHEN 3 THEN ‘Image’
    WHEN 4 THEN ‘Linked Report’
    WHEN 5 THEN ‘Data Source’
    WHEN 6 THEN ‘Model’
    ELSE ‘Type:’+CONVERT(varchar(2),c.Type)
    END [TypeDesc]
, Parent.Name [ParentName]
, c.CreationDate
, c.ModifiedDate
, ISNULL(c.Hidden, 0) as Hidden
, Parent.Name as Parent_Name
FROM dbo.Catalog c
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = c.ParentID
ORDER BY c.Path, Parent.Name
–tsql to list the assigned users/roles to each FOLDER
SELECT
c.Path
, u.UserName
, r.RoleName
FROM dbo.Catalog c
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = c.ParentID
INNER JOIN dbo.PolicyUserRole pur ON pur.PolicyID = c.PolicyID
INNER JOIN dbo.Users u ON u.UserID = pur.UserID
INNER JOIN dbo.Roles r ON r.RoleID = pur.RoleID
WHERE c.Type IN (
1 — Folder
)
ORDER BY c.Path, u.UserName

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s