Dynamically Control Data Filtering in SQL Server Reporting Services Reports

By:   |   Comments (20)   |   Related: > Reporting Services Dynamic Reports


Problem

I have a number of reports that anyone in the organization can view.  These reports are coming from a SQL Server 2005 database.  However, there are limitations on the data that a person can access.  A person may be able to see all data, data from just their office, or data from a specific list of offices. How can I go about implementing this security scheme in SQL Server Reporting Services (SSRS)? 

Solution

Based on your requirements I would suggest that you setup SSRS security such that you run your reports as a particular domain account then filter the data in the report based on who is actually running the report.  For example, create a domain account called ReportViewer and put this account in the db_datareader role in the reporting database.  Configure the report data source to run as this account, allowing the report read-only access to the data regardless of who is actually running the report.  By using this approach you do not have to allow the users running the reports any access to the reporting database, since the ReportViewer account is the one actually connecting to the database.

You can get the credentials of the user that is running the report from the User!UserID global variable provided by SSRS.  You'll need a table where you specify the offices that a user is allowed to access; we'll call it UserOffice.  You will include an inner join to the UserOffice table in your report queries to limit the user to seeing data for the offices that they are allowed to see.  You will also filter the UserOffice table to only include the rows for the user that is running the report.

Add the ReportViewer User to the db_datareader Role

Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance that contains your reporting database.  In my case the database is named mssqltips_reporting.  Expand the SQL Server instance in the Object Explorer then expand the Security node.  Right click on Logins and create a login for the ReportViewer domain account.  Add the account to the db_datareader role for the reporting database as shown below:

reportviewer dblogin

Note that my domain is vs.local; all references to user accounts and groups in this tip will therefore begin with "vs\" followed by the user account or group.

Create the UserOffice Table

The UserOffice table specifies the list of offices that a user can access in a report.  Let's assume we have an Office table that looks like this:

CREATE TABLE [dbo].[Office](
 [OfficeKey] [smallint] IDENTITY(1,1) NOT NULL,
 [OfficeName] [nvarchar](50) NOT NULL
)

The UserOffice table would look like this:

CREATE TABLE [dbo].[UserOffice](
 [UserOfficeKey] [smallint] IDENTITY(1,1) NOT NULL,
 [UserAccount] [nvarchar](50) NOT NULL,
 [OfficeKey] [smallint] NOT NULL
) 
ALTER TABLE dbo.UserOffice
ADD CONSTRAINT pk_UserOffice 
PRIMARY KEY CLUSTERED (UserAccount, OfficeKey)

An example of a value in the UserAccount column would be vs\joe.  This matches what SSRS will return as the credentials of the user running the report.

The primary key for the UserOffice table is made up of the UserAccount and OfficeKey columns so that there can't be any duplicates (which would cause our report queries to select data multiple times when joining on the OfficeKey).  In addition the primary key is clustered since the typical usage of this table will be to query the OfficeKey values for a particular user.

Creating a Report

We'll create a very simple SSRS report using the Business Intelligence Development Studio (BIDS) to show how to filter the report data based on who is running the report.  We will use the following query for our report:

SELECT f.Year, o.OfficeName, f.SalesAmount
FROM dbo.Sales f
JOIN dbo.UserOffice u ON u.OfficeKey = f.OfficeKey
JOIN dbo.Office o ON o.OfficeKey = u.OfficeKey
WHERE u.UserAccount = @REPORTUSER
ORDER BY f.Year, o.OfficeName

The parameter @REPORTUSER is defined in the Report Parameters as having a default value which is specified as the expression: =User!UserID   SSRS sets the global variable User!UserID to the credentials of the user that is running the report.  This allows us to filter our query based on who is running the report.

We will use a Matrix layout for our report as follows:

report layout

The text box at the bottom of the report layout will show the credentials of the user running the report.  This will allow us to verify that the report is returning the appropriate data.

We'll deploy the report to the Report Manager using the Deploy option in BIDS. 

SSRS Setup

After creating and deploying the report, there are a few things to do in the SSRS setup.  We need to allow users to access the Report Manager and configure the report data source to connect to the reporting database as the ReportViewer account.

Log in as an administrator and launch the Report Manager.  Report Manager is the web application that is included with SSRS.   Assuming a default configuration, open your browser and navigate to http://servername/reports, where servername is the server where SSRS is deployed.  Click on Site Settings; you should see the Site Settings hyperlink in the top right of your browser as shown below:

site settings

Click on "Configure site-wide security"; you will see this in the Security section as shown below:

config site settings

Click the New Role Assignment button on the toolbar then fill in the dialog as shown below (remember to use your domain name instead of vs):

system role

In this step we are allowing everyone from a particular Active Directory group to be in the System User role.  The Domain Users group is a built-in one that includes all users.  You may choose a group that is more restrictive than this one.  You can also add users individually if necessary. 

In addition to being a member of a System role, users also need to be assigned to a role which determines the tasks they can perform such as running reports, publishing reports, etc.  Return to the Report Manager home and click the Properties tab, then click the New Role Assignment button.  Fill in the dialog as shown below:

ssrs role

For our purposes we are making all users a member of the Browser role which allows them to run reports.  You could choose a more restrictive group or add users individually if necessary.

Our last SSRS setup step is to edit the data source used by our report so that it always uses the ReportViewer account to connect to the reporting database.  To do this navigate to your report Data Source in the Report Manager and set the credentials as shown below:

data source credentials

In effect what we are saying is no matter who is running the report, SSRS will connect to the reporting database as the vs\reportviewer account (remember my domain is vs).  At the beginning of this tip we added the vs\reportviewer account to the db_datareader role in our reporting database, giving this user read-only access to our data.

Running the Report

In order to test the report, login as a particular user or launch Internet Explorer by right clicking on it, selecting Run As, and entering the credentials to use.  I have setup two accounts for my test: Tom has access to all offices and Joe is restricted to just seeing two offices.  The results of running the report as Tom are:

tom

The results of running the report as Joe are:

joe

As you can see, we are able to filter the data based on who is running the report.

Next Steps
  • Download the sample report project and experiment with it.  The archive includes a backup of the database used in the tip which you can restore.  Edit the UserOffice table to include the credentials of users in your domain.
  • You may be able to use this approach to implement a security scheme where you have to filter data based on who is running the report.  You could easily extend this sample to include parameter dropdown lists which are also filtered based on the user running the report.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, November 15, 2018 - 8:18:57 PM - Ray Barley Back To Top (78269)

The data is filtered based on the user who is running the report.  If you can login with another user's account then you could see their data.  

But you shouldn't be logging in as someone other than yourself. 

Wednesday, November 14, 2018 - 9:41:48 AM - Pihong Back To Top (78240)

This is an excellent example to show different data for different people using the same report. But I have a question I don't know if this can prevent one user inputs another user account he can also see another user's data?

Thursday, September 13, 2018 - 9:18:22 AM - Ray Barley Back To Top (77537)

 I haven't used SSRS 2017 yet but as long the User!UserID global variable is still available the approach will work.

I haven't used PBRS so I don't know.

Thursday, September 13, 2018 - 1:36:03 AM - Benjamin Bartlett Back To Top (77533)

 Could this same method be utlized for SSRS/PBRS 2017?

 

Monday, January 7, 2013 - 7:09:49 AM - Vivek Back To Top (21292)

thanks for your note Ray,

but I have tried this option already but its not helping much.... Dataset filter is not applicable since data vaires project to project..

Friday, January 4, 2013 - 9:55:54 AM - Raymond Barley Back To Top (21260)

There is an axis property called scale break.  SSRS will automatically adjust the axis values to try and make the chart appear normal when your data has some outliers.  Go to the axis (horizontal or vertical) properties, axis options, and click the Enable scale breaks checkbox.

I don't know how to do this dynamically; maybe you could filter out the bad data from your dataset.

Friday, January 4, 2013 - 12:00:57 AM - Vivek Back To Top (21255)

Hi Ray,

I'd like to filter my SSRS Chart data on the fly. i.e., I'd let my end user to filter data range. say for instance, my chart data varies 0.1 to 100 but chart is getting screwed with some bad data 453243.09 

Kindly let me know the possibilities.

 

Wednesday, October 17, 2012 - 10:13:21 AM - Ray Barley Back To Top (19960)

SSAS security only supports Windows authentication.  The way that you might be able to get what you need is the connection string has an EffectiveUserName property that you can set and SSAS will impersonate that user for you.

You can get started by looking at this post: http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=18

I have done a few tips on other aspects of SSAS security; you can see the list here: http://www.mssqltips.com/sqlserverauthor/14/ray-barley/

Wednesday, October 17, 2012 - 8:06:56 AM - Mubin Shaikh Back To Top (19954)

Hi ,

This is really good article but i am stuck on issue as given below can you please guide me on this

Problem Scenario is:

 

our SSRS is configured to use form authentication, we have few users in aspnetdb through that we are doing login to view reports.

To view data from cube we have given static credential in data source which will be used for all users of aspnetdb, So Reports which are based on Cube shows all the dimension data to all user. Now we want to restrict user to view limited values of dimensions according to his login/user id and permissions given. so we need some custom security configuration between ssrs and ssas.

 Can you please guide or provide link of your blog post on this, or some helpful links to resolve this.

 

Thanks in Advance,

Mubin

 

Wednesday, February 1, 2012 - 10:12:29 AM - Ray Barley Back To Top (15866)

You are correct.  Per this page: http://msdn.microsoft.com/en-us/library/ms156307.aspx

The report must use stored credentials or no credentials to retrieve data at run time. You cannot subscribe to a report that is configured to use the impersonated or delegated credentials of the current user to connect to an external data source. The stored credentials can be a Windows account or a database user account. 

Monday, January 30, 2012 - 4:17:31 PM - Rob Back To Top (15841)

Note this doesn't work at all for report subscriptions.

 

Monday, January 9, 2012 - 2:57:29 PM - Ray Barley Back To Top (15568)

An object's visibility is controlled by the Hidden property.  You set it to true or false.  You can set the value of the Hidden property based on an expression; e.g. =IIF(Parameters!ShowData.Value=1,True,False)  In this case I have a report parameter named ShowData.

You can set the value of the ShowData parameter by specifying that the available values come from a query then specify a dataset that returns a single row with a single column.  You need a query (or a stored proc) that returns 1 if you want to show the data and 0 (or any other value) if you don't want to show the value.

Set the visibility of the ShowData parameter to Hidden; you don't want to prompt the user for it.

Monday, January 9, 2012 - 2:20:21 PM - Jerry Back To Top (15566)

This is very useful, and thank you very much.  I wonder if you could help a bit further.  Similar to other scenarios listed, I have a report with data that is broken into different areas.  One area is machine specific data, the other is Human Resources specific data.  Members of one AD Group might have access to all the data, while members of another AD Group only one set of the data.  I have put together a SP that will return members of the AD group if provided the group name.  My problem is trying to run this in an expression that is tied to Visibility.

IF OBJECT_ID('tempdb..#tempsams') IS NOT NULL BEGIN DROP TABLE #tempsams END
SELECT  *
INTO  #tempsams
FROM  OPENQUERY(ADSI, 'SELECT SamAccountName, Name, SN, Mail FROM ''LDAP://DC=123,DC=123,DC=123456,DC=com''
        WHERE memberOf=''CN=GroupName,OU=FolderName,OU=Groups,DC=123,DC=123,DC=123456,DC=com'' AND
 objectCategory=''Person'' AND objectClass = ''USER''') AS SAMACCOUNTTABLE
SELECT  CASE WHEN SamAccountName IS NOT NULL THEN 'Domain\' + SamAccountName End as DomainName
FROM  #tempsams AS TMP WITH (nolock)

Could you help please?

Monday, January 9, 2012 - 12:53:01 PM - Ray Barley Back To Top (15562)

I'm going to answer the question - How do I set the visible property (i.e. Hidden = True or False) of an object in a report from the result of a query

Create a dataset that returns a single row; it can call a stored proc or just execute a SQL query.  Let's call this ShowData.

Create a hidden parameter that gets its list of available values from the ShowData dataset.  Essentially you want the dataset to return a value that will be assigned to the hidfden parameter.

Use the hidden parameter in an expression that sets the value (True or False) of the Hidden property for an object.

 

Thursday, August 20, 2009 - 6:27:21 AM - raybarley Back To Top (3928)

Two ideas come to mind.  First, you can specify an expression for your dataset query string.  From the Dataset tab, click on the button with the 3 dots right next to the dataset name drop down, then click on the fx button next to the query string text box.  You would use the expression something like this:

=IIF(Parameters!REPORTUSER.Value = "vs\Mary",
"SELECT f.Year, o.OfficeName, f.SalesAmount
FROM dbo.Sales f
JOIN dbo.UserOffice u ON u.OfficeKey = f.OfficeKey
JOIN dbo.Office o ON o.OfficeKey = u.OfficeKey
ORDER BY f.Year, o.OfficeName",
"SELECT f.Year, o.OfficeName, f.SalesAmount
FROM dbo.Sales f
JOIN dbo.UserOffice u ON u.OfficeKey = f.OfficeKey
JOIN dbo.Office o ON o.OfficeKey = u.OfficeKey
WHERE u.UserAccount = @REPORTUSER
ORDER BY f.Year, o.OfficeName")

However, when I do this it doesn't work;  I get an message when I try to run the report.  I think this is possible but I don't know the answer.

My second idea is to create a table valued function that takes the @REPORTUSER as a parameter and returns a resultset that includes all of the rows that the user is allowed to see.  In this example you would return the list of OfficeKey and OfficeName then replace the join to the Office table with a join to your table valued function.  Inside of your table valued function you can do whatever logic you need, such as returning all rows for a particular user, or returning selected rows based on joining to the UserOffice table for other users.  I think this is better than an expression which was my first thought.

 

Wednesday, August 19, 2009 - 12:08:44 PM - max420 Back To Top (3920)

Hi

First of all thank you very much for this post... It helped me big time...

Now, I just had a quick follow up question on this.... Everything works exactly as you mentioned in there... I just wanted to know if there is anyway to add an exception on that query in the report..

As in WHERE u.UserAccount = @REPORTUSER except if the @REPORTUSER is "xxx".... If @REPORTUSER is "xxx" then show everything....

Please help!! Would really appreciate it...

Thanks

MAX

Tuesday, August 18, 2009 - 6:46:24 AM - admin Back To Top (3903)

This has been fixed in the tip, but you can download it here:

http://www.mssqltips.com/tipimages2/1743_ssrs_security_sample.zip

Monday, August 17, 2009 - 12:10:38 PM - carlyk Back To Top (3896)

 How do I access the download for this? It prompts me for a user name and password. I tried my MSSQLTips login but that did not work.


Thursday, July 30, 2009 - 6:26:38 PM - raybarley Back To Top (3817)

Basing the security on groups rather than individuals is a better approach; the tip was based on an actual project where they already had a security mechanism by user and not role.  As the tip points out you can determine who the user is in SSRS but there isn't anything built-in that I'm aware of   As you suggest you could have an OfficeGroup table but you'll have to write some .Net code to find out if the user belongs to a particular group.

I did another tip where I showed an SSIS package that extracted some data from Active Directory to populate role-based security tables in a relational database.  That link is here: http://www.mssqltips.com/tip.asp?tip=1657  In the next steps of the tip there is an additional download where the sample code was changed around some to be a little easier.  Take a look at the forum entry on Mon, Mar 16 2009 5:57 AM  if you want the details about the revised code.

 

Thursday, July 30, 2009 - 12:11:11 PM - edward Back To Top (3816)

I had a questoin regarding the article. Instead of configuring the data filter based on a user can I do the same thing based on a user's group or role? Thus, I would have a OfficeGroup table. Is this possible in reporting services?

 Thank you for your time.

Edward














get free sql tips
agree to terms