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:

       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)
RETURN SELECT 1 as fn_SecureCustomerData
-- filter out records based on database user name 
where @FilterColumnName = user_name();

 ADD FILTER PREDICATE dbo.fn_RowLevelSecurity(SalesPersonUserName)
 ON dbo.Customer

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.

Latest Articles