5/02/2011

Sample Query's ReportServer LINK

Here's some good links to query the ReportServer

- Click Here for Link -

To get User Permissions by Folder
SELECT
c.[Name]
, u.[UserName]
, r.[RoleName]
FROM [ReportServer].[dbo].[PolicyUserRole] pur
LEFT JOIN [ReportServer].[dbo].[Users] u on pur.[userID]=u.[userID]
LEFT JOIN [ReportServer].[dbo].[Roles] r on pur.[roleID]=r.[roleID]
LEFT JOIN [ReportServer].[dbo].[Catalog] c on pur.[policyID]=c.[policyID]
WHERE [RoleName] = 'Browser'
AND c.[TYPE] = 1
AND c.[Hidden] <> 1
AND c.[Name] <> 'Data Sources'
ORDER BY c.[Name], u.[UserName], r.[RoleName]

To Get Folder Names

SELECT DISTINCT
c.[Name]
FROM [ReportServer].[dbo].[PolicyUserRole] pur
LEFT JOIN [ReportServer].[dbo].[Users] u on pur.[userID]=u.[userID]
LEFT JOIN [ReportServer].[dbo].[Roles] r on pur.[roleID]=r.[roleID]
LEFT JOIN [ReportServer].[dbo].[Catalog] c on pur.[policyID]=c.[policyID]
WHERE [RoleName] = 'Browser'
AND c.[TYPE] = 1
AND c.[Hidden] <> 1
AND c.[Name] <> 'Data Sources'
ORDER BY 1

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.