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.
