I would like to create a single report that filters data based on what Active Directory group the user is in.
Through various searches I see that Row Level Security is possible in SSRS in the Report
Model. However, I can't seem to find any instructions for SSRS 2008 R2. They all seem to be for 2005 and it seems that Microsoft has removed any sort of feature like this in 2008 along with most of their articles.
Is this even possible anymore? If not, what's the alternative? I don't want to have to create a report per group and I don't want people to see data that is not available to them.
Do I need to somehow handle this in a View? If so, how do I get TSQL and AD to talk? Will the user's SSRS credentials be passed to the view?
Best Answer
SSRS in the Report Model is available in sql 2008 R2, but not in express edition
check http://msdn.microsoft.com/en-us/library/ms365305(SQL.105).aspx
and here is how to apply it on sql 2008 R2 http://msdn.microsoft.com/en-us/library/ms365343(SQL.105).aspx
and if you want to create a view and get TSQL and AD to talk
you can use the following functions to get username
or if you want to get data from ADSI, you will need to create a linked server connection to ADSI and get users info http://blog.namwarrizvi.com/?p=254 http://www.kodyaz.com/articles/active-directory-services-queries-using-openquery.aspx
and use the where clause
finally check this article "SSRS 2008 R2: Implementing Row-level Security" http://www.sqlservercentral.com/blogs/sqldownsouth/2011/10/13/ssrs-2008-r2_3A00_-implementing-row_2D00_level-security/