Dynamically Control Data Filtering in SQL Server Reporting Services Reports

By: Ray Barley | Read Comments (11) | Print Ray is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert. Related Tips: More

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:

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:

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:

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

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):

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:

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:

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:

The results of running the report as Joe are:

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.

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code