Using userid/role in SSRS report

1
votes

Ok, I answered my first question as I was trying to figure out how to phrase this post -- I can include the Windows login in a SQL Server Reporting Services 2008R2 report by using the User!Userid variable.

So, the next question is, assuming I define a custom SSRS role QAEmps, can I access that role from within a report to only display the full report to someone in that server role, and to just show anyone else their own data? Or do I have to do two reports, one that includes everyone and can only be accessed by QAEmps, and another that can be accessed by anyone, but is limited to their own data?

Thanks!

2
reporting-servicesssrs-2008

2 Answers

2
votes

You can find SSRS role membership information for the roles you have defined as well as for the system-defined roles (Content Manager, etc.) by querying your ReportServer database:

select Roles.RoleName, Users.UserName
from PolicyUserRole
join Roles
on PolicyUserRole.RoleId = Roles.RoleId
join Users
on Users.UserId = PolicyUserRole.UserId
2
votes

Why not pass the UserId into the query/stored procedure and filter the results based upon the UserId permissions?