Wednesday, March 7, 2012

How to know who has access to which report

What query may I use?
OlegHow about something like this:
-- Query ReportServer to display RS objects and who has access to them
-- Brian Katz - www.bolign.com
SELECT c.Name,
case c.Type
when 1 then 'Folder'
when 2 then 'Report'
when 3 then 'Resource'
when 4 then 'LinkedReport'
when 5 then 'DS'
end as Type,
u.UserName as PermittedUser,
RoleName,
p.Name as Parent,
case p.Type
when 1 then 'Folder'
when 2 then 'Report'
when 3 then 'Resource'
when 4 then 'LinkedReport'
when 5 then 'DS'
end as ParentType
FROM catalog c
join catalog p on c.ParentID = p.ItemID
join [ReportServer].[dbo].[PolicyUserRole] pur on c.PolicyID =pur.PolicyID
join dbo.Users u on u.UserID = pur.UserID
join dbo.Roles r on r.RoleID = pur.RoleID
where c.Type in (1, 2, 4)
order by c.Name

No comments:

Post a Comment