December 1, 2008 at 3:53 am
Hi,
Is there a way I can generate list of reports to which a user has access to using T-SQL running against ReportServer Database?
Deepak
December 2, 2008 at 8:33 am
Use this SQL on the server your SSRS is running on:
USE ReportServer
GO
-- List users\accounts associated with SSRS reports
SELECTCASE IsNull(c.Path,'') WHEN '' THEN 'Home' ELSE c.Path END 'Path',
r.RoleName,
u.UserName
FROMdbo.Catalog c
INNER JOIN dbo.PolicyUserRole p ON p.PolicyID = c.PolicyID
INNER JOIN dbo.Roles r ON r.RoleID = p.RoleID
INNER JOIN dbo.Users u ON u.UserID = p.UserID
WHEREc.Type = 2 -- 1,5=folder, 2=report, 3=data source
ORDER BY 1, 2, 3
This lists the security that is contained within the SSRS environment specifically, but the 'UserName' can actually be a group account so you will need to query further into the Active Directory world (which can be done from within T-SQL but I don't have examples) if you need to see what specific accounts are part of the account groups shown. This works in SQK2000 but I believe it should work in SQL2005 also.
Cheers,
maddog
June 17, 2010 at 9:26 am
Thanks a lot. That helped me out. 😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply