{"id":7993,"date":"2019-12-06T14:29:36","date_gmt":"2019-12-06T14:29:36","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/row-level-security-in-sql-server-2016\/"},"modified":"2026-11-03T19:29:12","modified_gmt":"2026-11-03T19:29:12","slug":"row-level-security-in-sql-server-2016","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/de\/blogs\/row-level-security-in-sql-server-2016\/","title":{"rendered":"Row-Level Security in SQL Server 2016"},"content":{"rendered":"<p><strong>Introduction<\/strong><\/p>\n<p>Microsoft\u00a9 has focused on security in SQL Server\u00a9, and almost all releases&nbsp;either have an enhancement to existing features or have introduced new security&nbsp;features. In SQL Server 2016, Microsoft introduced many new security features&nbsp;that help users protect their data, including Row-Level Security, Always&nbsp;Encrypted, and Dynamic Data Masking.. In this blog, I introduce the Row-Level Security (RLS) feature, which enables&nbsp;you to control which users have access to rows in a table. RLS enables you to&nbsp;implement restrictions on the data based on the characteristics of the user who&nbsp;is executing a query. RLS helps you to control access to data easily with&nbsp;complete transparency for different users.<\/p>\n<p><strong>The need for RLS<\/strong><\/p>\n<p>There are times when we need to return only selective data to selective users.In the past, we did that by creating views and granting user select permission&nbsp;on those views. However, that approach became unmanageable with the number of views&nbsp;required to accommodate the growing volume of data and number of users. So&nbsp;Microsoft introduced RLS to meet the security requirement in the new circumstances.<\/p>\n<p>RLS enables fine-grained control over access to rows in a table, enabling you&nbsp;to easily control which users can access which data with complete transparency&nbsp;to the application.<\/p>\n<p>With this feature, rows are filtered based on the execution context of the query,&nbsp;rather than the current user access rights. You can create secure logic rules to&nbsp;determine which user can see which rows and restrict any rows (or data) by&nbsp;designing a flexible and robust security policy for a table, as shown in the&nbsp;following image:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26567-body-Picture1_1.png\" alt=\"row level security pic 1\" \/><\/p>\n<p><a href=\"https:\/\/sqlwithmanoj.com\/2015\/07\/13\/implementing-row-level-security-rls-with-sql-server-2016\/\"><em>Image Source<\/em><\/a><\/p>\n<p><strong>&nbsp;RLS properties<\/strong><\/p>\n<ul>\n<li>&nbsp; &nbsp;Fine-grained access role (control both read and write access to specific rows)<\/li>\n<li>&nbsp; &nbsp;Application transparency (no application changes are required)<\/li>\n<li>&nbsp; Centralize the access within the database<\/li>\n<li>&nbsp; Easy to implement and maintain<\/li>\n<\/ul>\n<p><strong>&nbsp;How RLS works&nbsp;<\/strong><\/p>\n<p>To implement the RLS, you need to consider the following elements:<\/p>\n<ul>\n<li>&nbsp; Predicate function<\/li>\n<li>&nbsp;Security predicates<\/li>\n<li>&nbsp;Security policy<\/li>\n<\/ul>\n<p>The following sections describe these items.<\/p>\n<p><strong>Predicate function<\/strong><\/p>\n<p>A predicate function is an in-line table value function that checks whether a<br \/>\nuser executing a query has access to data based on logic defined on it. This<br \/>\nfunction returns `<em>1<\/em>` for each row that a user is permitted to access.<\/p>\n<p><strong>Security predicates<\/strong><\/p>\n<p>The security predicate helps in binding the predicate function to the table.&nbsp;RLS supports two types of security predicates: Filter predicates and block&nbsp;predicates. The filter predicate filters the data silently without raising any&nbsp;error for the following operations according to the logic defined in the&nbsp;predicate function.<\/p>\n<p>\n<em><strong>&nbsp;`SELECT`<br \/>\n&nbsp;`UPDATE`<br \/>\n&nbsp;`DELETE`<\/strong><\/em><\/p>\n<p>The block predicate explicitly raises an error and blocks the user from using&nbsp;the following operations on data that violates the predicate function logic:<\/p>\n<ul>\n<li><em>&nbsp;AFTER INSERT<\/em><\/li>\n<li><em>&nbsp;AFTER UPDATE<\/em><\/li>\n<li><em>&nbsp;BEFORE UPDATE<\/em><\/li>\n<li><em>&nbsp;BEFORE DELETE<\/em><\/li>\n<\/ul>\n<p><strong>Security policy<\/strong><\/p>\n<p>A security policy object gets created for RLS grouping all the security predicates that referenced the predicate function.<\/p>\n<p><strong>Use cases<\/strong><\/p>\n<p>Here are some design examples of how RLS can be used:<\/p>\n<ul>\n<li>&nbsp; &nbsp;A hospital can create a security policy that allows nurses to view data rows for their patients only.<\/li>\n<li>&nbsp; &nbsp; A bank can create a policy to restrict access to financial data rows based&nbsp;&nbsp; &nbsp;on an employee&#8217;s business division or role in the company.<\/li>\n<li>&nbsp; &nbsp; &nbsp;A multi-tenant application can create a policy to enforce a logical separation&nbsp; &nbsp;of each tenant&#8217;s data rows from every other tenant&#8217;s rows. Because data for&nbsp;many tenants is stored in a single table, the process is more efficient.<\/li>\n<li>&nbsp; &nbsp;Each tenant can see only its data rows.<\/li>\n<\/ul>\n<p><strong>Implement RLS<\/strong><\/p>\n<p>Following is an example of how to implement RLS:<\/p>\n<p>Step 1: Run the following code to create a database `RowFilter` and two users<br \/>\nto test with:<\/p>\n<p>&nbsp; &nbsp;<em> CREATE DATABASE RowFilter;<br \/>\n&nbsp; &nbsp; GO<br \/>\n&nbsp; &nbsp; USE RowFilter;<br \/>\n&nbsp; &nbsp; GO<br \/>\n&nbsp; &nbsp; CREATE USER userBrian WITHOUT LOGIN;<br \/>\n&nbsp; &nbsp; CREATE USER userJames WITHOUT LOGIN;<br \/>\n&nbsp; &nbsp; GO<\/em><\/p>\n<p>Step 2: Run the following code to create a table with examples and grant the<br \/>\nSELECT privilege to the new users:<\/p>\n<p><em>&nbsp; &nbsp; CREATE TABLE dbo.SalesFigures (<br \/>\n&nbsp; &nbsp; [userCode] NVARCHAR(10),<br \/>\n&nbsp; &nbsp; [sales] MONEY)<br \/>\n&nbsp; &nbsp; GO<br \/>\n&nbsp; &nbsp; INSERT &nbsp;INTO dbo.SalesFigures<br \/>\n&nbsp; &nbsp; VALUES (&#8216;userBrian&#8217;,100), (&#8216;userJames&#8217;,250), (&#8216;userBrian&#8217;,350)<br \/>\n&nbsp; &nbsp; GO<br \/>\n&nbsp; &nbsp; GRANT SELECT ON dbo.SalesFigures TO userBrian<br \/>\n&nbsp; &nbsp; GRANT SELECT ON dbo.SalesFigures TO userJames<br \/>\n&nbsp; &nbsp; GO<\/em><\/p>\n<p>Step 3: Run the following code to add a filter predicate function:<\/p>\n<p><em>&nbsp; &nbsp; CREATE FUNCTION dbo.rowLevelPredicate (@userCode as sysname)<br \/>\n&nbsp; &nbsp; RETURNS TABLE<br \/>\n&nbsp; &nbsp; WITH SCHEMABINDING<br \/>\n&nbsp; &nbsp; AS<br \/>\n&nbsp; &nbsp; RETURN SELECT 1 AS rowLevelPredicateResult<br \/>\n&nbsp; &nbsp; WHERE @userCode = USER_NAME();<br \/>\n&nbsp; &nbsp; GO<\/em><\/p>\n<p>Step 4: Run the following code to add a filter predicate to the table<br \/>\n<strong>dbo.Sales Figures<\/strong>:<\/p>\n<p><em>&nbsp; &nbsp; CREATE SECURITY POLICY UserFilter<br \/>\n&nbsp; &nbsp; ADD FILTER PREDICATE dbo.rowLevelPredicate(userCode)<br \/>\n&nbsp; &nbsp; ON dbo.SalesFigures<br \/>\n&nbsp; &nbsp; WITH (STATE = ON);<br \/>\n&nbsp; &nbsp; GO<\/em><\/p>\n<p>Step 5: Run the following code to test the results with a user that was added<br \/>\nin Step 2:<\/p>\n<p><em>&nbsp; &nbsp; EXECUTE AS USER = &#8216;userBrian&#8217;;<br \/>\n&nbsp; &nbsp; SELECT * FROM dbo.SalesFigures;<br \/>\n&nbsp; &nbsp; REVERT;<br \/>\n&nbsp; &nbsp; GO<\/em><\/p>\n<p>\nThis code returns two rows, as shown below:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26567-body-Picture2_1.png\" alt=\"row level security pic 2\" \/><\/p>\n<p>&nbsp;<em> EXECUTE AS USER = &#8216;userJames&#8217;;<br \/>\n&nbsp; &nbsp; SELECT * FROM dbo.SalesFigures;<br \/>\n&nbsp; &nbsp; REVERT;<br \/>\n&nbsp; &nbsp; GO&nbsp;<\/em><\/p>\n<p>This code returns one row, as shown below:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26567-body-Picture2_1.png\" alt=\"row level security pic 2\" \/><\/p>\n<p><strong>&nbsp;&nbsp;Permissions<\/strong><\/p>\n<ul>\n<li>Creating, altering, or dropping security policies requires the <strong>ALTER ANY&nbsp;SECURITY POLICY<\/strong> permission.<\/li>\n<li>Creating or dropping a security policy requires the <strong>ALTER<\/strong> permission on the&nbsp;schema.<\/li>\n<li>Additionally, each predicate requires the following permissions, which are&nbsp;added:<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>Security policies apply to all users, including database owner (DBO) users in&nbsp;the database. DBO users can alter or drop security policies. However, their&nbsp;changes to security policies can be audited. In the case of high-privileged&nbsp;users such as `<em>sysadmin<\/em>` or `<em>db_owner<\/em>`, you need to see all rows to troubleshoot&nbsp;or validate data, so you must write the security policy to allow that.<\/p>\n<p>If a security policy is created with <em>SCHEMABINDING = OFF<\/em>, then users must have&nbsp;<em>SELECT<\/em>&nbsp;or <em>EXECUTE<\/em>&nbsp;permission on the predicate function and any additional&nbsp;tables, views, or functions used within the predicate function to query the&nbsp;target table. If a security policy is created with <em>SCHEMABINDING = ON<\/em>, which&nbsp;is the default, then these permission checks are bypassed when users query the<br \/>\ntarget table.<\/p>\n<p><strong>&nbsp;Modifying SQL Server 2016 RLS<\/strong><\/p>\n<p>\nPerform the following action to disable SQL Server RLS for a policy:<\/p>\n<p>&#8211;&nbsp;&nbsp; &nbsp;Alter security policy <strong>UseFilter<\/strong>&nbsp;with <em>State = off<\/em>.<\/p>\n<p>Perform the following actions to drop the filter and security policy:<\/p>\n<p>&#8211;&nbsp;&nbsp; &nbsp;Drop security policy<strong>&nbsp;UseFilter.<br \/>\n&#8211;&nbsp;&nbsp; &nbsp;<\/strong>Drop function <strong>dbo.rowlevelPredicate.<\/strong><\/p>\n<p><strong>&nbsp;Best practices<\/strong><\/p>\n<p>&#8211;&nbsp;&nbsp; &nbsp;We highly recommended that you create a separate schema for the RLS objects<\/p>\n<p>&nbsp; &nbsp; &nbsp; (predicate function and security policy).<\/p>\n<p>&#8211;&nbsp;&nbsp; &nbsp;The <strong>ALTER ANY SECURITY POLICY<\/strong>&nbsp;permission is intended for highly-privileged<\/p>\n<p>users (such as a security policy manager). The security policy manager does not<\/p>\n<p>require <strong>SELECT<\/strong> permission on the tables they protect.<\/p>\n<p>&#8211;&nbsp;&nbsp; &nbsp;Avoid type conversions in predicate functions to avoid potential runtime errors.<\/p>\n<p>&#8211;&nbsp;&nbsp; &nbsp;Avoid recursion in predicate functions wherever possible to avoid performance<\/p>\n<p>degradation. The query optimizer tries to detect direct recursions. However, it is<\/p>\n<p>not guaranteed to find indirect recursions (such as when a second function calls<\/p>\n<p>the predicate function).<\/p>\n<p>&#8211;&nbsp;&nbsp; &nbsp;Avoid using excessive table joins in predicate functions to maximize performance.<\/p>\n<p><strong>&nbsp;Limitation and restrictions in RLS<\/strong><\/p>\n<p>\n&#8211;&nbsp;&nbsp; &nbsp;The predicate function must be created with `<em>SCHEMABINDING<\/em>`. If a&nbsp;function is created without `<em>SCHEMABINDING<\/em>`, and you try to bind it to a security&nbsp;policy, it throws an error.<\/p>\n<ul>\n<li>&#8211;&nbsp;&nbsp; &nbsp;Indexed views cannot be created on a table on which RLS is implemented.<\/li>\n<li>&#8211;&nbsp;&nbsp; &nbsp;In-memory tables are not supported for RLS.<\/li>\n<li>&#8211;&nbsp;&nbsp; &nbsp;Full-text indexes are not supported.<\/li>\n<\/ul>\n<p><strong>Conclusion<\/strong><\/p>\n<p>With the RLS feature in SQL Server 2016, you can provide security for records&nbsp;at the database level without making changes at the application level. You can&nbsp;implement RLS by using a predicate function and the new security policy feature&nbsp;alongside your existing code, without needing to change the Data&nbsp;Manipulation Language (DML) code in your database.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Row-Level Security in SQL Server 2016<\/p>\n","protected":false},"author":2,"featured_media":7995,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26567","content_type":"blog","created_by":"10130","authored_on":"2024-02-01T14:29:36+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Vishal Srivastava, SQL Server Database Administrator, Rackspace Technology","seo_description":"Implement SAP Web Dispatcher for load distribution across your landscape with minimal configuration and granular control over security and performance.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26567-insights_image-erp-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"SAP, Microsoft SQL","product":"","publisheddate":"2019-12-06T14:29:36","rxt_solutions":"Enterprise Resource Planning (ERP), Security","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Row-Level Security in SQL Server 2016 | Rackspace Technology","field_topics":"","field_slug":"row-level-security-in-sql-server-2016","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7993","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/7993","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/comments?post=7993"}],"version-history":[{"count":3,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/7993\/revisions"}],"predecessor-version":[{"id":9807,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/7993\/revisions\/9807"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media\/7995"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media?parent=7993"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/categories?post=7993"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/tags?post=7993"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}