How to do Row Level Security in SSRS 2008 R2 (or 2012) with Active Directory Groups?

Question

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?

Answer 1

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

select suser_name() as 'usrname'
or select suser_sname() as sname
or select system_user as system_

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/