Simple Row Level Security for SQL Server 2016

Have you ever had a need to place some simple row level security on a SQL Server table?  Meaning you only want certain people to select or update a row.  Well now you can do that in SQL Server 2016 by using row level security.  To implement row level security all you need to do is create a filter predicate, which is just a function, and have a security policy that references that function.  Let me show you how to accomplish this.

Suppose you have a table named dbo.Customers that looks like this:

CREATE TABLE Customer(
       CustomerName varchar(100) NULL,
       CustomerEmail varchar(100) NULL,
       SalesPersonUserName varchar(20) NULL
);  

Where you only want the sales person to be able to update their rows of data in the Customer table, but you don’t want them to update records of other sales people.   A sales person row is identified by having their user name in the SalesPersonUserName column for a row.  To implement row level security in SQL Server 2016, all I need to do is restrict access to rows in the Customer table by creating the following FUNCTION and  SECURITY POLICY:

CREATE FUNCTION fn_RowLevelSecurity (@FilterColumnName sysname)
RETURNS TABLE
WITH SCHEMABINDING
as
RETURN SELECT 1 as fn_SecureCustomerData
-- filter out records based on database user name 
where @FilterColumnName = user_name();

 CREATE SECURITY POLICY FilterCustomer
 ADD FILTER PREDICATE dbo.fn_RowLevelSecurity(SalesPersonUserName)
 ON dbo.Customer
 WITH (STATE = ON); 
 
 

By creating these two objects in a SQL Server 2016 environment I only allow a user to see or update records in the Customer table if their user name is in the SalesPersonUserName column.   

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles