In this blog I will explain and demonstrate how to leverage a new feature in SQL Server 2016, Row Level Security (RLS) with SSRS.  Detailed steps including the TSQL script and SSRS project is provided at the end of this post.  To get started, you need to setup RLS.  In this example I will use Windows Authentication when connecting to the data source (SQL Server).  Therefore, the first step is to create a Login on the SQL Server from a Windows Account:

USE [master]

GO

CREATE LOGIN [domainusername]

FROM WINDOWS

GO

After the login is created, the next step is to create a User in the database that is associated to the data that requires RLS. 

USE [databasename]

GO

CREATE USER [UserName]

FOR LOGIN [domainusername];

At this point there are a couple paths that can be taken.  For the sake of brevity and so that we are all on the same page I will provide sample scripts.  To start run the following statement:

DROP TABLE IF EXISITS dbo.Students

GO

CREATE TABLE dbo.Students

(

StudentID int,

SchoolRep sysname, -–This column will store the database username

Class varchar(20),

Grade char(1)

)

Next populate the table with some data by running this script:

–Add a few rows.  Any rows that include dbo as the SchoolRep will be return for those users that are dbo or system administrator

INSERT INTO dbo.Students

VALUES

(1, ‘<DATABASE USERNAME>’, ‘College Algebra’, ‘A’),

(2, ‘<DATABASE USERNAME>’, ‘English 100’, ‘B’),

(3,’dbo’, ‘English 101’, ‘F’),

(4,’dbo’, ‘College Algebra’, ‘C’)

Replace DATABASE USERNAME with your user.  Now give the user read access to the table:

GRANT SELECT ON dbo.Students TO [UserName];

Now the fun begins.  Execute the following script in the same database where the table was created:

CREATE SCHEMA Security;

–Function that returns 1 when the SchoolRep column is the same as the user executing the query

CREATE FUNCTION Security.fn_securitypredicate(@SchoolRep AS sysname)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN SELECT 1 AS fn_securitypredicate_result

WHERE @SchoolRep = USER_NAME();

GO

–Policy that that adds the function as a filter predicate

CREATE SECURITY POLICY SchoolFilter

ADD FILTER PREDICATE Security.fn_securitypredicate(SchoolRep)

ON dbo.Students

WITH (STATE = ON);

GO

You can test your implementation by executing the following script:

EXECUTE AS USER = ‘Database UserName’  –Replace with your User

SELECT * FROM dbo.Students;

REVERT;

That was not that bad.  Now let’s get to SSRS.  I am going to assume that everyone can create a Data Source, Dataset and a report that leverages that Dataset. Deploy all three to your Report Server.  Once the data source is deployed open it in Report Manager and verify that the radio button labeled Windows integrated security is selected.  The Data Source should resemble the following:

Finally, connect to the Report Manager or web portal (New In SQL Server 2016) as the windows user and run the report.  You should only see the rows that contain that user as the SchoolRep. Like I said, a perfect match.

Steps to Reproduce (as of the writing of this blog)

1.  Install SQL Server 2016 CTP 3.3

2.  Create a database or use an existing database

3.  Download and run this script.  Please read the comments.  You will be required to adjust certain items to fit your environment.

4.  Install SQL Server Data Tools 2015

5.  Download this SSDT 2015 SSRS Project.

6.  Run the ReadMe report and follow the outlined steps.

7.  Deploy the report to your report server.

8.  Connect to the Report Manager or web portal (new in 2016) as the user and run the report.

Wow, I cannot wait to help a customer get this implemented into a production environment.

Talk to you soon,

Patrick LeBlanc