SSRS 2008 - Implementing Row Level Security

Question

SSRS 2008 - Implementing Row Level Security

WebTechie

SSChampion

Points: 11405
More actions
October 20, 2011 at 9:44 am

#245632

Hello,
I have a report that was developed using SQL Server/SSRS 2008. The user loved it.
But then he came back with, "Can everyone see all the data in the report?"
Yes, if they can see the folder, they can see the report. Can we lock the report down so that only employees from a facility can see data for that facility?
This is a new request for me.
Does anyone know of any tutorials or blogs that would explain how to implement row level security for a SSRS report?
If anyone could point me in the right direction, I would greatly appreciate it.
I would like to implement the following:
FacilityReport
Rowid employee facility location
1 a 1 Somewhere1
2 b 1 Somewhere1
3 c 2 Somewhere2
When employee "c" views the report, he should only see row 3.
Again, if anyone could point me in the right direction, I would greatly appreciate it.
Thanks,
Tony
Things will work out. Get back up, change some parameters and recode.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

okbangas SSChampion Points: 11773 More actions · Answer 1

Quick and dirty "right direction":

You'll have to have some kind of table controlling user info and access. Then you would typically use suser_name() in your query to retrieve the user id of the user executing the report (requring windows integrated authentication in the database connection), and match this against the access table and your other data.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

okbangas (10/21/2011)
Quick and dirty "right direction":
You'll have to have some kind of table controlling user info and access. Then you would typically use suser_name() in your query to retrieve the user id of the user executing the report (requring windows integrated authentication in the database connection), and match this against the access table and your other data.

Exactly what i was going to suggest;

here's a snippet/example, where a view is filtered by susuer_name and that extra table he mentioned.

-- Create and Populate Regions Table

declare @Regions table (RegionName varchar(30) not null)

insert into @Regions

select 'North America' union all

select 'Latin America' union all

select 'Europe' union all

select 'Africa' union all

select 'Middle East' union all

select 'Far East' union all

select 'South East'

-- create table to hold assignments based on user and Region Name

-- The UserName in this table should be the login that the users

-- are using to connect ot SQL Server. Ideally it would be their

-- Windows Login account, but it could also be their SQL Login

-- account if not using Windows Authentication

declare @users table (UserName varchar(30) not null, RegionName varchar(30))

insert into @users

select 'SQLLogin','North America' union all

select 'SQLLogin','Africa'

-- syntax for view. This will use the system function suser_name

--for the login name to compare to what is in the table

-- Create view UserRegions as

select r.RegionName

from @Regions R inner join @Users U

on R.RegionName = u.RegionName

where u.UserName = suser_name()

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WebTechie SSChampion Points: 11405 More actions · Answer 3

Thank you gentlemen.

I was hoping for a different way. However, if that is the prescribed way of handling such a request, guess I have to do it.

Thanks again.

Things will work out. Get back up, change some parameters and recode.