Reporting Services – Filtering Data by Current User

You’ve hit the home stretch of a data warehouse solution for your organization.  Congratulations, as this is no small feat!  All of the dimensions and measures are built and populated, bringing together data from several systems including systems from subsidiaries or partners.  The parent company will benefit from a great solution, as they will get a full picture of all of the subsidiary businesses.  The final reports are being implemented and approved by the users: It looks like smooth sailing from here.

However, refinements may still be required. Here’s an example.

One of my users in a subsidiary of Twin Cities Tacos, Edina Tacos, is testing Reporting Services reports, and generating data related to other local taco franchises of Twin Cities Tacos.  Edina Tacos has no interaction with the other franchises, and has no interest in that data.  She shares this feedback with her primary stakeholders, who agree the franchises should not see data from other franchises.  Only employees working directly for Twin Cities Tacos should be able to see data across the enterprise.  To the development team, this is definitely a late-breaking development that is sure to frustrate some, as everything was built to specification.  The reports need to filter data according to which user is running the reports, so she sees only the data she needs.

How can this be accomplished in a timely manner without creating considerable re-work?  This blog will investigate two methods to filter data, based on the user running the report.  Of course, there are other solutions that can be implemented, but we’ll focus on two recommended solutions in this post.

1. Updating queries to filter data by username
2. Implementing Row Level Security

Note: Both solutions require either Windows authentication to Reporting Services and to SQL Server OR SQL authentication with the user being prompted to enter their credentials when a report is executed.  A single login from Reporting Services to SQL Server (Windows or SQL authentication) will not work for either solution, as SQL Server needs to know the unique user running queries. 

Both solutions require a mapping table to be created to aid in the filtering of data.  The table needs to link a unique ID for each company to the name of a role or Active Directory group to which each group of users will be added.  An example of a mapping table is shown below and will be used in queries throughout this blog.

create table [dbo].[CompanyRoleMapping]
(
	MappingId int identity(1,1) primary key,
	CompanyId int not null,
	RoleName nvarchar(50) not null
)

Solution #1 – Explicit Filtering in SQL Queries

Since SQL Server 2008, SQL Server has provided functionality to determine if a user belongs to specific user group(s), user-defined and database fixed roles.  IS_MEMBER is the function that checks both Active Directory groups and SQL Server roles for the logged-in user to the SQL session.  SQL Server 2012 split up the functionality into two functions, IS_ROLEMEMBER for user-defined and database fixed roles in SQL Server, and IS_SRVROLEMEMBER.  The two functions also allow a login to be provided, in the event that information is passed to the session and is available.  Otherwise, both functions will base the results on the currently logged-in user.

One of the reports shows basic sales information for each customer over the past month.

select CustomerName, ReportingPeriod, ProductName, sum(Qty), sum(Total)
from Fact.TacoSales as t
	inner join Dim.Customer c on t.DimCustomerId = c.DimCustomerId
	inner join Dim.Product p on t.DimProductId = p.DimProductId
	inner join Dim.Date d on t.DimSalesDateId = d.DimSalesDateId
where d.CalendarDate > dateadd(month, -1, getdate()) and 
d.CalendarDate <= getdate()

To limit the data to a specific group of users, the same query may look like the following query.  The CompanyRoleMapping table is now being joined, and the data is being filtered, based on the Windows account’s group memberships in Active Directory.

select CustomerName, ReportingPeriod, ProductName, sum(Qty), sum(Total)
from Fact.TacoSales as t
	inner join Dim.Customer c on t.DimCustomerId = c.DimCustomerId
	inner join Dim.Product p on t.DimProductId = p.DimProductId
	inner join Dim.Date d on t.DimSalesDateId = d.DimSalesDateId
	inner join dbo.CompanyRoleMapping cmr 
on (c.CompanyId = cmr.CompanyId or cmr.CompanyId = 1)	
			--CompanyId 1 is Twin Cities Tacos, who can see all data.
where d.CalendarDate > dateadd(month, -1, getdate()) and 
d.CalendarDate <= getdate() and 
is_srvrolemember(cmr.Rolename) = 1

Tip: If the query is being run on a system using SQL Authentication, rather than Mixed Authentication, the above query using IS_SRVROLEMEMBER needs to be changed to use IS_ROLEMEMBER.

If the user has access to the data warehouse but is not associated with any groups, the zero records will be returned to the report.

Solution #2 – Row Level Security in SQL Server

SQL Server 2016 introduced Row Level Security (RLS) to apply filtering during the query process in a seamless manner.  A security policy is defined on table(s) for a defined column, like Company ID or DimCompanyId in the warehouse.  The security policy uses a user-defined function to determine whether or not the user can view a particular record.  The implementation of this process is defined below.

Important Note – RLS policies apply to ALL SQL logins and users, including administrators.  To be able to query data in the table, everyone must be assigned to a role.  Users who are internal employees must be in a role that has access to all data.  I’ve called this TwinCitiesTacos in my example. 

To simplify the security process, it is recommended database roles be created for each group of users, with an Active Directory (AD) group assigned to the role.  The following logic makes this assumption.

A security predicate function must be created to perform the “security check”.  The function needs to query CompanyRoleMapping by CompanyId and by the role(s) the user has to find a match.  Here is an example of this function.

create function dbo.udf_RLSCompanyIdPredicate(@DimCompanyId int)
returns table
with schemabinding
as
	return select 1 as fn_securitypredicate_result
		from dbo.CompanyRoleMapping crm
			inner join Dim.Company c on crm.CompanyId = c.CompanyId
		where is_rolemember(RoleName) = 1 and 
             	      (CompanyId = @CompanyId or @CompanyId = 1)
go

Once the function has been created, it needs to be bound to the table(s) requiring security, based on the example listed here.  The table must contain a field that represents DimCompanyId in the data warehouse.  If there are multiple ways to distinguish a company in different areas of the warehouse, multiple predicate functions need to be created, along with multiple security policies for each predicate.

create security policy DimCompanyPredicate add filter predicate 
	dbo.udf_RLSPredicate(DimCompanyId) on Fact.TacoSales with (state = on)

At this point, RLS has been set up for the tables requiring it.  When different users run the same query against the data mart, they will receive data for the CompanyIds to which they have access.

select CustomerName, ReportingPeriod, ProductName, sum(Qty), sum(Total)
from Fact.TacoSales as t
	inner join Dim.Customer c on t.DimCustomerId = c.DimCustomerId
	inner join Dim.Product p on t.DimProductId = p.DimProductId
	inner join Dim.Date d on t.DimSalesDateId = d.DimSalesDateId
where d.CalendarDate > dateadd(month, -1, getdate()) and 
d.CalendarDate <= getdate()

The above solution made use of the is_rolemember() function (and can be substituted with is_srvrolemember()).   For these functions to work correctly, the users logging into the system cannot belong to a fixed server role, besides public.  When a user belongs to a fixed server role, the calls to these functions will return NULL.  If there are users who have to be in both a fixed server role and in a database role for RLS, a second method to implement RLS is required.  To begin implementing this variation, below is the predicate function, re-written for our needs.

CREATE FUNCTION [dbo].[udf_RLSDimAgentIDPredicate](@DimAgentId INT)
RETURNS TABLE
AS
	
RETURN SELECT 1 AS fn_securitypredicate_result
		FROM Dim.Agent a 
			LEFT JOIN dbo.CompanyRoleMapping crm 
				ON crm.CompanyId = c.CompanyId OR
				   crm.CompanyId = -1
			LEFT JOIN sys.database_principals r 
				ON crm.RoleName = r.name
			LEFT JOIN sys.database_role_members ur 
				ON r.principal_id = ur.role_principal_id 
			LEFT JOIN sys.database_principals u
				ON ur.member_principal_id = u.principal_id 
		WHERE u.name = ORIGINAL_LOGIN() AND a.DimAgentId = @DimAgentId
GO

In this function, please note the following: First, the function is not defined with SCHEMABINDING.  By default, Row Level Security requires SCHEMABINDING on all objects, but there is a flag that can override this setting, as you’ll see in the creation of the security policy.  This change is required, as SCHEMABINDING cannot be applied to objects referencing system objects, in this case the role-related system views.

CREATE SECURITY POLICY DimCompanyPredicate add filter predicate dbo.udf_RLSPredicate(DimCompanyId) on Fact.TacoSales WITH (schemabinding=off, status=on)

Consider the different restrictions and functionality available with Row Level Security. Below is a table listing the two implementation types, along with their pros and cons.

Solution Comparison

1 – Assumes the security predicate function has been written correctly and the Agent/Role mapping table is secured and managed properly.

Hopefully, these solutions have given you some ideas to implement data filtering based on the user currently running a report. The scope of your report library and your company’s security policies will dictate your overall filtering solution.