Home > SQL Server 2016 > Implementing “Row Level Security” (RLS) with “Filter Predicate” in SQL Server 2016 – Part 1

Implementing “Row Level Security” (RLS) with “Filter Predicate” in SQL Server 2016 – Part 1

For an Organization its Data is the most important thing, and the Org’s IT department takes lot of measures to make sure data does not fall in wrong hands. The DBA’s and Application programmers setup different layers of security on top of data so that the user is only able to see the Filtered data/rows from a table that he has access to. DB programmers normally create abstracted Views or Stored Procedures with complex logic on top of the Tables by using other master-mapping tables that contains the user-data access key. Sometimes these security logics are not upto the standards, and may have various versions on a database and thus it becomes difficult to track, update and make them fail-proof. So, with SQL Server 2016 the new Row Level Security feature is going to handle this Out of the Box and as a Standard.

RLS or Row Level Security is a feature that enables fine grained control over access to rows in a table, allowing you to easily control which users can access which data with complete transparency to the application.

With this feature rows are filtered based on the Execution Context of the Query, rather than the current user access rights. A secure logic can be created to determine which user can see which rows and restrict any kind of data (rows) by designing a flexible and robust Security policy for a table.

SQL Server 2016 - Row Level Security 04

–> To setup Row Level Security (RLS) on a particular table it needs only these simple steps:

1. Create Users and Grant Read/SELECT access for a Particular table.

2. Create a new Inline Table-Valued Function that will contain the Filter Predicate for that table. This Function Predicate can be a sophisticated business logic with multiple JOINs or a simple WHERE ManagerCode = ‘userHR’.

3. Create a new Security Policy for this table and add the above Function (Filter) Predicate to it.

Please note: that these Functions & Security Policies should be unique for a table. So to create RLS for an another table, you will need to create separate Function & Security Policy.

–> Step 1.a. Let’s create some test accounts: I will create three users for:

1. The CEO, over-all admin of the company data.

2. HR department head

3. Finance department head



–> Create a sample table [dbo].[Employee]: with a self-referencing Manager ID column.

CREATE TABLE dbo.Employees (
	[EmpCode] NVARCHAR(50),  -- Employee ID
	[EmpName] NVARCHAR(250), -- Employee/Manager Full Name
	[Salary]  MONEY,		 -- Fictious Salary
	[MgrCode] NVARCHAR(50)   -- Manager ID

-> Now insert some test records:

-- Top Boss CEO
INSERT INTO dbo.Employees VALUES ('userCEO'	, 'CEO Top Boss'  , 800, NULL)

-- Next 2 levels under CEO
INSERT INTO dbo.Employees VALUES ('userHR'	, 'HR User'		  , 700, 'userCEO');
INSERT INTO dbo.Employees VALUES ('userFin'	, 'Finance User'  , 600, 'userCEO');

-- Employees under Kevin
INSERT INTO dbo.Employees VALUES ('manojp'	, 'Manoj Pandey'  , 100, 'userHR');
INSERT INTO dbo.Employees VALUES ('saurabhs', 'Saurabh Sharma', 400, 'userHR');
INSERT INTO dbo.Employees VALUES ('deepakb' , 'Deepak Biswal' , 500, 'userHR');

-- Employees under Amy
INSERT INTO dbo.Employees VALUES ('keshavk'	, 'Keshav K'	  , 200, 'userFin');
INSERT INTO dbo.Employees VALUES ('viveks'	, 'Vivek S'		  , 300, 'userFin');

–> Let’s check the records before applying “Row Level Security”:

SELECT * FROM dbo.Employees; -- 8 rows

As a normal SEELCT and without RLS, it just ignores my Execution Context and execute the Query and return all the 8 rows.

–> The Traditional way to setup the Row Level Security till now was as follows (a simple example):

-- Stored Procedure with User-Name passed as parameter:
CREATE PROCEDURE dbo.uspGetEmployeeDetails (@userAccess NVARCHAR(50))
	FROM dbo.Employees
	WHERE [MgrCode] = @userAccess
	OR @userAccess = 'userCEO'; -- CEO, the admin should see all rows

-- Execute the SP with different parameter values:
EXEC dbo.uspGetEmployeeDetails @userAccess = 'userHR'  -- only 3 rows
EXEC dbo.uspGetEmployeeDetails @userAccess = 'userFin' -- only 2 rows
EXEC dbo.uspGetEmployeeDetails @userAccess = 'userCEO' -- all 8 rows

The above method is prone to issues, like SQL Injection and any other user can apply other user’s User-Name and get the information that he is not allowed to see. With this type of method you have to apply another security layer at the application level so whenever a user executes the SP it gets executed with the same user’s User-Name.

–> The new Row Level Security feature let you:
– apply this security at the database level
– and there is no need to apply the WHERE clause filter for the User-Name.

This makes the security system more reliable and robust by reducing the surface area of your security system.

–> Step 1.b. Grant Read/SELECT access on the dbo.Employee table to all 3 users:

GRANT SELECT ON dbo.Employees TO userCEO;
GRANT SELECT ON dbo.Employees TO userHR;
GRANT SELECT ON dbo.Employees TO userFin;


–> Step 2. Let’s create an Inline Table-Valued Function to write our Filter logic:

CREATE FUNCTION dbo.fn_SecurityPredicateEmployee(@mgrCode AS sysname)
    RETURN SELECT 1 AS fn_SecurityPredicateEmployee_result
	-- Predicate logic
	WHERE @mgrCode = USER_NAME() 
	OR USER_NAME() = 'userCEO'; -- CEO, the admin should see all rows

This function returns value 1 when:

– a row in the MgrCode (i.e. the Manager Code) column is the same as the user executing the query (@@mgrCode = USER_NAME())

– or if the user executing the query is the Top Boss user (USER_NAME() = ‘userCEO’)

–> Step 3. Create a security policy adding the function as a filter predicate:

ADD FILTER PREDICATE dbo.fn_SecurityPredicateEmployee(MgrCode)  -- Filter Column from dbo.Employee table
ON dbo.Employees
WITH (STATE = ON); -- The state must be set to ON to enable the policy.

The above Security Policy takes the Filter Predicate Logic from the associated Function and applies it to the Query as a WHERE clause.

–> Now let’s again check the records after applying “Row Level Security”:

SELECT * FROM dbo.Employees; -- 0 rows, 

The simple “SELECT *” statement will fetch me zero rows after applying RLS, as my user ID is not configured to have access to any of those rows & Table.

–> And if you check in the Execution Plan of above SELECT statement without WHERE clause, it will show you the Filter Predicate that is added by the Security Policy defined in Step #3 for applying RLS on this table, which looks like this:

OR user_name()=N’userCEO’

SQL Server 2016 - Row Level Security 10

–> Let’s check the 3 users we created and provided them customized access to the dbo.Employee table and rows in it:

-- Execute as our immediate boss userHR (3 rows): 
SELECT * FROM dbo.Employees; -- 3 rows

-- Execute as our immediate boss userFin: 
EXECUTE AS USER = 'userFin';
SELECT * FROM dbo.Employees; -- 2 rows

-- Execute as our Top boss userCEO (8): 
SELECT * FROM dbo.Employees; -- 8 rows

–> The results of the above 3 SELECTs looks like this:

SQL Server 2016 - Row Level Security 01

So, as you can see the three users we created resulted in different results:

– The HR & Finance users got just 3 & 2 rows, respectively.

– But the admin CEO user got all the 8 rows.

–> Thus, by using RLS Filter Predicate feature in SQL Server 2016 you can create your own customized Security by creating an Inline Table-Valued Function that is linked to the Security Policy for your Table.

–> Check Block Predicates with RLS in my [next post, Part 2].

–> Final Cleanup

DROP SECURITY POLICY [dbo].[ManagerFilter]
DROP FUNCTION [dbo].[fn_SecurityPredicateEmployee]
DROP TABLE [dbo].[Employee]

DROP PROCEDURE dbo.uspGetEmployeeDetails


Check the same demo on YouTube:


Check & Like my FB Page

  1. Keshav Singh
    July 14, 2015 at 11:51 am

    Great article, very well explained, lucid. A new feature to take away.

  2. Suhas
    June 19, 2016 at 4:02 pm

    Hi Manoj,
    This is definitely one of the very well written article with example on RLS. Thanks for taking time to write this.
    One humble request –
    There is a small typo which needs correction. Within the Stored Proc -dbo.uspGetEmployeeDetail, please make the table name as employees on line no. 6. It was throwing an error saying employee table doesn’t exist.

    • June 20, 2016 at 10:34 am

      Thanks Suhas, for pointing it out. corrected !!!

  3. Suhas
    June 19, 2016 at 4:05 pm

    One Question Manoj. What is the performance impact of doing this?

    • June 20, 2016 at 10:33 am

      Well regarding perf I didn’t not see any improvement nor any significant degradation. But I think there would be scope of pref-issues depending on how you are going to query the data.

      • suhas
        June 22, 2016 at 10:45 am

        Thanks Manoj!

  4. January 8, 2017 at 12:31 pm

    Very good informative stuff!


  5. April 11, 2017 at 3:56 pm

    Hi Manoj,
    I can achieve the same by using View. So my question is why we are going to use row level security ? Please refer the below query for your reference .

    CREATE TABLE Employee(Id INT,Name VARCHAR(10))

    INSERT INTO Employee(Id,Name) VALUES(1,’Prasad’),(2,’Priyan’),(3,’Rajeev’)

    SELECT * FROM Employee



    SELECT * FROM dbo.Employee WHERE Name =USER_NAME()–IN(‘Prasad’,’Priyan’)

    INSERT INTO VW_Emp VALUES(4,’Dumas’)



    GRANT SELECT ON dbo.VW_Emp TO Prasad

    EXECUTE AS USER=’Prasad’
    SELECT * FROM dbo.VW_Emp

    DROP TABLE Employee


  6. December 17, 2017 at 7:37 pm

    Thank you sir. I am a systems architect studying for 70-473. This is one of the topics of the exam. Since I do not have any DBA experience, it took me some time to understand this from the Microsoft documentation but it definitely got VERY clear after reading this in your site. Congratulations and thanks for people like you that makes other people’s life easier because the effort they put while sharing knowledge. Keep the hard work !

  7. Ayan Mullick
    May 15, 2018 at 11:42 pm

    Thanks for the blog.

    This mentions the recommendations that RLS be implemented thru a new schema. Could you update to accommodate best practice?

  8. Pavas
    April 5, 2019 at 4:22 am

    Great Article, How can we achieve row level security for multiple users?

    • Pavas
      April 5, 2019 at 4:24 am

      How can we achieved Row Level Security with multiple users for one row

    • April 6, 2019 at 3:15 am

      For multiple users you will have to use a security group having all similar users in that group.

      • Pavas
        April 8, 2019 at 8:44 am

        Thanks for your reply!
        I created security groups Admin which should have access to all the rows, also other security groups, where similar users are grouped together. In below code column Employee Group has the group name which states users in that group should access the row. I tried below code but it did not work. Any thoughts? Thanks

        CREATE FUNCTION dbo.fn_SecurityPredicateEmployee (@EmployeeGroup AS sysname)

        RETURN SELECT 1 AS accessResult
        WHERE IS_MEMBER(‘Domain\Admin’) = 1 OR IS_MEMBER(‘Domain\’+@EmployeeGroup) = 1;


  9. sdsdsd
    December 15, 2020 at 11:03 pm

    Hi ,

    Im getting below Error;

    Msg 343, Level 15, State 1, Line 57
    Unknown object type ‘SECURITY’ used in a CREATE, DROP, or ALTER statement.
    Msg 102, Level 15, State 1, Line 58
    Incorrect syntax near ‘FILTER’.

    • December 15, 2020 at 11:10 pm

      What is your SQL Server version? RLS works on 2016 and above.

  10. Nag
    August 30, 2021 at 11:54 am

    Hi Manoj,
    Thanks for the great article. Wondering if the same feature can be extended to lookup of multiple columns ? (i.e., for a user fetch all matching records based on several columns present in user access table)

  1. July 20, 2015 at 7:00 am
  2. January 9, 2016 at 9:35 pm
  3. January 5, 2017 at 12:05 pm
  4. July 6, 2022 at 1:13 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: