Groups | Blog | Home
all groups > sql server reporting services > august 2005 >

sql server reporting services : How to know who has access to which report


Oleg Bulay
8/8/2005 5:41:04 PM
What query may I use?

Oleg

BKOct NO[at]SPAM shaw.ca
8/8/2005 5:57:08 PM
How 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
AddThis Social Bookmark Button