How to Set Up and Audit Row-Level Security in SQL Server SQL Server row-level security (RLS) controls which rows a database user can read or...
SQL Server row-level security (RLS) controls which rows a database user can read or change. A database administrator (DBA) writes the access rule as a function inside the database and attaches it to the table. SQL Server then applies that rule to every query against the table, whichever application or tool sent it, so two users can run the identical SELECT and get different result sets.
This guide covers the SQL Server database engine feature. Power BI has a separate feature with the same name. Power BI row-level security filters a semantic model for report viewers through DAX roles and has no effect on queries sent directly to SQL Server.
RLS uses two objects. The first is an inline table-valued function (a function that returns its result as a table), called the security predicate. It takes column values from a row and returns a result only when the current user may access that row. The second is a security policy, created with CREATE SECURITY POLICY, which attaches the function to a table so SQL Server evaluates it on every query.
According to Microsoft’s row-level security documentation, the predicate can check group membership or execution context, such as the database user name or a value the application stores in session context.
Microsoft introduced the feature in SQL Server 2016 (13.x). The same mechanism is available in Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, and the SQL surfaces in Microsoft Fabric. For quick definitions of the terms used throughout this guide, keep the row-level security glossary open alongside it.
One function can serve in a policy as a filter predicate, a block predicate, or both. Filter predicates silently remove rows a user cannot access from read operations, which Microsoft defines as SELECT, UPDATE, and DELETE, since UPDATE and DELETE must first find the rows they change. The user gets no error and no hint that other rows exist.
Block predicates reject write operations that fail the function’s check, and each one covers a single operation. The four block operations are AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, and BEFORE DELETE. The AFTER predicates check a row’s new values, and the BEFORE predicates check the row as it exists before the change.
With only a filter predicate, a sales rep can insert an order assigned to a different rep, and the row disappears from their results as soon as the insert commits. The filter also does nothing to stop an UPDATE that reassigns one of their visible rows to another owner. An AFTER INSERT block predicate and an AFTER UPDATE block predicate stop both writes, and the walkthrough below uses both.
A multitenant application that stores every customer in shared tables can filter on a tenant ID column to keep each tenant’s rows separate, the design described in DZone’s article on multi-tenant data isolation. In healthcare, Microsoft’s example is a hospital that lets nurses view only their own patients’ rows. In finance and sales, a policy can restrict rows by business division, role, or region, so a regional sales rep sees that region’s orders while a sales manager sees all of them.
In the walkthrough below, a DBA builds a complete policy on a small orders table where each sales rep owns their rows, and a sales manager sees everything. The steps follow Microsoft’s examples and community tutorials such as Pinal Dave’s SQL Authority walkthrough. Run the walkthrough in a scratch database, because the cleanup step drops every object it creates.
Microsoft highly recommends keeping RLS objects in their own schema, apart from the tables they protect. With that separation, a DBA can grant ALTER on the security schema to a small group without giving them rights over the data tables. The script creates a Sales schema for data, a Security schema for the predicate and policy, and three users without logins for testing.
CREATE SCHEMA Sales;
GO
CREATE SCHEMA Security;
GO
CREATE TABLE Sales.Orders
(
OrderID int IDENTITY(1,1) PRIMARY KEY,
SalesRep sysname NOT NULL,
Region nvarchar(50) NOT NULL,
Product nvarchar(50) NOT NULL,
Quantity int NOT NULL
);
INSERT INTO Sales.Orders (SalesRep, Region, Product, Quantity)
VALUES (N'SalesRep1', N'East', N'Valve', 5),
(N'SalesRep1', N'East', N'Wheel', 2),
(N'SalesRep2', N'West', N'Seat', 8),
(N'SalesRep2', N'West', N'Valve', 3);
CREATE USER SalesManager WITHOUT LOGIN;
CREATE USER SalesRep1 WITHOUT LOGIN;
CREATE USER SalesRep2 WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales.Orders
TO SalesManager, SalesRep1, SalesRep2;
GO
Each user can read, insert, update, and delete rows in the table, so any restriction they hit during testing comes from the policy.
The policy passes the SalesRep column to the predicate function. The function returns a row when the current user may see that order and returns nothing when they may not.
CREATE FUNCTION Security.fn_SalesRepPredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_result
WHERE @SalesRep = USER_NAME()
OR USER_NAME() = N'SalesManager';
GO
WITH SCHEMABINDING locks the objects the function references, so no one can drop or alter them in a way that breaks the function. USER_NAME() returns the database user running the query, which is the user that EXECUTE AS switches to during testing. We hard-coded the manager’s user name to keep the example short. In production, use a role membership check or a lookup table instead.
CREATE SECURITY POLICY attaches the function to Sales.Orders. The script adds it once as a filter predicate and twice as a block predicate, for AFTER INSERT and AFTER UPDATE, and STATE = ON turns the policy on immediately.
CREATE SECURITY POLICY Security.SalesOrdersPolicy
ADD FILTER PREDICATE Security.fn_SalesRepPredicate(SalesRep) ON Sales.Orders,
ADD BLOCK PREDICATE Security.fn_SalesRepPredicate(SalesRep) ON Sales.Orders AFTER INSERT,
ADD BLOCK PREDICATE Security.fn_SalesRepPredicate(SalesRep) ON Sales.Orders AFTER UPDATE
WITH (STATE = ON);
GO
To suspend enforcement without dropping anything, run ALTER SECURITY POLICY Security.SalesOrdersPolicy WITH (STATE = OFF). The policy’s own SCHEMABINDING option defaults to ON, and the permissions section explains what that setting changes.
EXECUTE AS USER switches the session to each test user, and REVERT switches it back.
EXECUTE AS USER = 'SalesRep1';
SELECT OrderID, SalesRep, Region, Product FROM Sales.Orders;
REVERT;
EXECUTE AS USER = 'SalesRep2';
SELECT OrderID, SalesRep, Region, Product FROM Sales.Orders;
REVERT;
EXECUTE AS USER = 'SalesManager';
SELECT OrderID, SalesRep, Region, Product FROM Sales.Orders;
REVERT;
SalesRep1 sees two rows, SalesRep2 sees the other two, and SalesManager sees all four. Next, test the block predicate by inserting, as SalesRep1, an order assigned to SalesRep2.
EXECUTE AS USER = 'SalesRep1';
INSERT INTO Sales.Orders (SalesRep, Region, Product, Quantity)
VALUES (N'SalesRep2', N'West', N'Wheel', 1); /* fails on the block predicate */
REVERT;
The insert fails with a block predicate error, because the new row’s SalesRep value does not pass the function for SalesRep1. Without the AFTER INSERT predicate, the same statement would succeed and the row would vanish from SalesRep1’s results. Once testing is done, drop the policy first, because the schema-bound policy depends on the function.
DROP SECURITY POLICY Security.SalesOrdersPolicy;
DROP FUNCTION Security.fn_SalesRepPredicate;
DROP TABLE Sales.Orders;
DROP USER SalesManager;
DROP USER SalesRep1;
DROP USER SalesRep2;
DROP SCHEMA Security;
DROP SCHEMA Sales;
GO
In a middle-tier design, the application connects to SQL Server through one shared login on behalf of all its users. USER_NAME() then returns the same value for every end user, so the predicate needs another way to tell them apart. The application stores the end user’s identity in session context with sp_set_session_context, and the predicate reads it back with SESSION_CONTEXT(). Microsoft’s RLS documentation uses this pattern for its middle-tier scenario. The example below assumes a table keyed on an integer SalesRepId column.
/* Run by the application right after it opens the connection */
EXEC sp_set_session_context @key = N'SalesRepId', @value = 17, @read_only = 1;
GO
CREATE FUNCTION Security.fn_AppUserPredicate(@SalesRepId AS int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_result
WHERE CAST(SESSION_CONTEXT(N'SalesRepId') AS int) = @SalesRepId;
GO
Setting @read_only = 1 protects the key from tampering. According to the sp_set_session_context reference, a read-only key cannot be changed again on that logical connection, so an attacker who injects SQL through the application cannot reset the key to another rep’s ID. Microsoft also limits session context to 1 MB in total, with keys up to 128 bytes and values up to 8,000 bytes.
Only accounts holding ALTER ANY SECURITY POLICY can create, alter, or drop a policy, and creating or dropping one also requires ALTER on the schema. The policy itself applies to every user, dbo included, unless the predicate explicitly exempts an account. Anyone who can alter the predicate function, impersonate a user with wider access, or edit a lookup table the predicate reads can still widen what they see.
Microsoft describes ALTER ANY SECURITY POLICY as a permission intended for highly privileged users acting as a security policy manager, so grant it to as few accounts as possible. To add a predicate to a policy, the account also needs these permissions:
Microsoft states that security policies apply to all users, including dbo users. A query against Sales.Orders as dbo during the test step returns no rows, because the predicate passes rows only for each row’s owner and for SalesManager. When members of sysadmin (the server-wide administrator role) or db_owner (the database owner role) need every row, add an explicit condition for them to the predicate, such as OR IS_MEMBER(N’db_owner’) = 1.
Treat that exemption as a documented security decision with an owner and a reason, because a reviewer will ask exactly who sees all rows and why. Not exempting privileged accounts is also a valid design, and it stops administrators from browsing restricted rows in queries they write by hand (ad hoc queries). A sysadmin can still disable or alter the policy, so keep an audit trail of every change to it.
Security policies default to SCHEMABINDING = ON. In that mode, SQL Server skips permission checks on the predicate function for the querying user, so a sales rep needs permission on Sales.Orders only, not on Security.fn_SalesRepPredicate or any table it reads.
With SCHEMABINDING = OFF, every user who queries the target table needs SELECT or EXECUTE on the predicate function and on each table, view, or function it references. Those extra permissions let more accounts read the lookup data, so keep the default unless a specific design can’t work with it.
Start by reviewing the existing policies. The sys.security_policies catalog view returns one row for each security policy in the database, and sys.security_predicates returns one row for each predicate. The query below joins the two and lists each policy’s name, enabled state, last modified date, target table, predicate type, operation, and predicate function.
SELECT
pol.name AS policy_name,
pol.is_enabled,
pol.modify_date AS modified_utc,
OBJECT_SCHEMA_NAME(pred.target_object_id) + N'.'
+ OBJECT_NAME(pred.target_object_id) AS target_table,
pred.predicate_type_desc,
pred.operation_desc,
pred.predicate_definition
FROM sys.security_policies AS pol
JOIN sys.security_predicates AS pred
ON pred.object_id = pol.object_id
ORDER BY policy_name, target_table, pred.predicate_type_desc;
An is_enabled value of 0 marks a policy someone turned off with STATE = OFF. The predicate_definition column holds the function name and the columns passed to it, such as Security.fn_SalesRepPredicate(SalesRep), with each name wrapped in square brackets. Filter predicates show NULL in operation_desc, and so does a block predicate created without a named operation, which covers every write operation.
Both catalog views cover only the current database, so run the query in each user database on the instance. According to Microsoft’s sys.security_predicates reference, the views show every row only to principals with ALTER ANY SECURITY POLICY, or with VIEW DEFINITION on the object.
When reviewing an RLS table, check these four paths, since each one can widen what a user sees:
Doing this by hand means querying database permissions, role memberships, and server-level grants on every instance, then repeating the work after each change. SQL Secure’s permission analysis shows what a DBA can access, and its risk reports list accounts with rights that can bypass a policy. Our article on the principle of least privilege in SQL Server explains the access model behind this review.
Microsoft notes that dbo-made security policy changes can be audited, and SQL Compliance Manager keeps that record. Its change auditing tracks DDL changes (statements that create, alter, or drop database objects), which covers CREATE, ALTER, and DROP SECURITY POLICY statements and changes to the predicate function. A reviewer who asks whether the policy changed outside change control gets a dated record of each change.
Yes, it can. A predicate adds work to every query against the protected table, and in some plans it stops SQL Server from splitting the query across multiple processor threads (parallelism). How much it costs depends on how the DBA writes the predicate function and how well the table’s indexes cover the columns the predicate and the workload filter on.
Microsoft recommends these rules for writing predicate functions:
In Brent Ozar’s March 2026 test, a query for the top 101 users in Las Vegas ran in about 1.4 seconds on an unsecured table using a parallel plan. Against an RLS-secured copy, the row-level security function blocked the parallel plan, and the query took about 3 seconds on a single thread. Ozar then indexed the Location column the query filters on, in both tables. The secured query still couldn’t run in parallel, but it now performed the same as the unsecured query, and the row-level security penalty disappeared. Single-row lookups by primary key stayed fast even before the index, so the cost lands on queries that read many rows.
An earlier MSSQLTips benchmark on SQL Server 2016 found that the predicate function drastically affected runtime even though the graphical execution plan gave no indication of it. Moving the predicate to SESSION_CONTEXT() made average performance worse in that test. Because the execution plan can hide the cost, compare measured duration and CPU for the same queries with the policy on and off before deploying.
Ozar concludes that indexing is more important than ever under RLS. The policy keeps the query on one thread even after the index exists, so the index has to make that single thread fast. Index the predicate’s filter column (SalesRep in the walkthrough) and the columns the workload filters by. Review the missing index recommendation before using it, because in Ozar’s test it keyed only on the query’s Location filter and left the column the RLS predicate checks out of the index key. A full scan of an unindexed table may seem tolerable while the table is small, but it slows down as rows accumulate, and the policy gives no warning.
RLS restricts which rows a query returns, but Microsoft documents two side-channel risks and a set of SQL Server features that cannot work on a table with a security policy. A side channel reveals hidden data indirectly, for example through an error message, without the query ever returning the row.
The first risk is a malicious security policy manager. Someone who can create security policies and inline table-valued functions could write a predicate designed to leak data through side channels, then collude with a user who has SELECT on the table. Microsoft recommends limiting permissions as needed and monitoring for suspicious activity. The permission review and audit trail described earlier put that guidance into practice.
The second risk is carefully crafted queries. A user who can run arbitrary SQL can write a query that raises an error, such as a divide-by-zero, only when a hidden row holds a particular value. The error reveals something about a row the filter never returned, so tables holding highly sensitive values need additional controls when users have ad hoc query access.
Five SQL Server features break or behave differently on a table with a security policy:
RLS fits when different users need different rows from the same table and every access path has to respect that rule. Views and application-layer filters restrict only the queries that go through them, so a user with direct table access skips them. SQL Server applies RLS to every query against the table, whichever application or tool sends it.
Dynamic data masking and column permissions are the two controls most often compared with RLS. Each one restricts a different part of the table.
| Control | What it restricts | What a restricted user sees |
| Row-level security | Rows | Only the rows the predicate allows, with no error for the rest |
| Dynamic data masking | Values in specific columns | Every row, with masked values such as XXXX in the masked columns |
| Column permissions | Entire columns | A permission error on any query that references a denied column |
According to Microsoft’s dynamic data masking documentation, a user with write permission can still update a masked column, and sysadmin and db_owner members always see the unmasked values. Microsoft calls masking complementary to RLS and auditing and recommends using them together. On one table, RLS can limit a sales rep to their own orders while masking hides a sensitive column within those orders.
Check the limits above before committing to it. An indexed view or a Filestream column already on the table rules RLS out, and a view-based or application-layer design becomes the fallback. If users run ad hoc queries against highly sensitive values, add controls for the side-channel risk. Plan to index the predicate’s filter columns, and measure the same queries with the policy on and off before deploying.
Verizon analyzed more than 22,000 confirmed breaches for its 2026 DBIR executive summary and found the human element in 62% of them. Third-party involvement appeared in nearly half, a 60% jump from the previous year. The report doesn’t measure whether RLS would have stopped any of those breaches, but RLS limits how many rows any single account can return, including accounts used by employees and partners.
On its own, RLS does not show who can switch it off. A sysadmin who runs ALTER SECURITY POLICY Security.SalesOrdersPolicy WITH (STATE = OFF) opens every order in the walkthrough table to anyone with SELECT on it, and the policy keeps no record of the change or of who made it.
We cover least privilege and auditing as companion controls in our post on SQL Server security best practices. In our database security compliance guide, we tie database access to PCI DSS Requirement 7, which restricts access to cardholder data by business need-to-know, and to the access control and audit controls standards in HIPAA 45 CFR 164.312.
SQL Secure shows who holds the rights that can bypass or disable a policy, and SQL Compliance Manager records the DDL and permission changes that would alter one, including that STATE = OFF statement.
Download free trials of SQL Secure and SQL Compliance Manager, and run both against the instance that will host the first production security policy.
Yes, through several documented paths. Anyone with ALTER ANY SECURITY POLICY can disable or rewrite the policy. A user with IMPERSONATE permission on an account with wider access can query as that account, and users with ad hoc query access can infer hidden values through error-based side channels. Microsoft’s countermeasure is to limit those permissions and monitor for suspicious activity.
No. RLS filters and blocks rows inside the database engine, but Microsoft documents side-channel leaks and incompatible features, and the policy itself doesn’t show who can change it. In production, pair it with least-privilege grants, auditing of policy and function changes, and performance measurements taken with the policy turned on.
SQL Server 2016 (13.x) and later versions support RLS, as do Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, and the SQL surfaces in Microsoft Fabric. RLS on PolyBase external tables requires SQL Server 2019 CU7 or later.
Use RLS when different users need different rows from the same table and every access path must respect that rule. Views and application-layer filters cover only the queries routed through them. If the table already depends on an indexed view or Filestream, a view-based or application-layer design is the fallback.
Contact our sales team to get a personalized demo of our database management software for SQL Server!
