How to Enable Row-Level Security (RLS) in SQL Server

Row-Level Security (RLS) in SQL Server

One of the security features available since SQL Server 2016 is Row-Level Security (RLS).
This feature allows you to control which rows are visible to each user.
While traditional permission management is table-based, with RLS you can restrict which rows are visible even within the same table, depending on the user.

How RLS Works

  • Apply a security policy to the table
  • Filter Predicate: automatically exclude rows that should not be visible
  • Block Predicate: control UPDATE/DELETE/INSERT operations

Preparing a Table for Testing


CREATE DATABASE [RLS_DB]
GO

USE [RLS_DB]
GO

-- Create table
CREATE TABLE dbo.SalesData (
    SaleID int,
    UserName sysname,   -- For SUSER_SNAME()
    Region sysname,     -- For SESSION_CONTEXT
    Amount int
);
GO

-- Insert sample data
INSERT INTO dbo.SalesData VALUES
(1, 'UserA', 'East', 100),
(2, 'UserB', 'West', 200),
(3, 'UserA', 'East', 150),
(4, 'UserB', 'West', 180),
(5, 'UserA', 'East', 120);
GO

Using SUSER_SNAME()

This approach controls row visibility based on the login name.
You store the login name in the table and compare it with SUSER_SNAME() inside a function.

Create Logins and Users


-- Create logins
CREATE LOGIN UserA WITH PASSWORD = 'Password123!';
GO
CREATE LOGIN UserB WITH PASSWORD = 'Password123!';
GO

USE [RLS_DB]
GO
-- Create database users
CREATE USER UserA FOR LOGIN UserA;
GO
CREATE USER UserB FOR LOGIN UserB;
GO

-- Grant permissions
GRANT SELECT ON dbo.SalesData TO UserA;
GO
GRANT SELECT ON dbo.SalesData TO UserB;
GO
GRANT INSERT, UPDATE, DELETE ON dbo.SalesData TO UserA;
GO
GRANT INSERT, UPDATE, DELETE ON dbo.SalesData TO UserB;
GO

Implementation Example (Filter + Block)


USE [RLS_DB]
GO

-- Filter Predicate function (controls which rows are visible)
CREATE FUNCTION dbo.fn_filterpredicate_suser(@UserName sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS AllowAccess
WHERE @UserName = SUSER_SNAME();
GO

-- Block Predicate function (prevents unauthorized DML)
CREATE FUNCTION dbo.fn_blockpredicate_suser(@UserName sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS AllowAccess
WHERE @UserName = SUSER_SNAME();
GO

-- Security policy (includes both filter and block)
CREATE SECURITY POLICY SalesFilter_Suser
ADD FILTER PREDICATE dbo.fn_filterpredicate_suser(UserName)
ON dbo.SalesData,
ADD BLOCK PREDICATE dbo.fn_blockpredicate_suser(UserName)
ON dbo.SalesData AFTER INSERT,
ADD BLOCK PREDICATE dbo.fn_blockpredicate_suser(UserName)
ON dbo.SalesData BEFORE UPDATE,
ADD BLOCK PREDICATE dbo.fn_blockpredicate_suser(UserName)
ON dbo.SalesData AFTER UPDATE,
ADD BLOCK PREDICATE dbo.fn_blockpredicate_suser(UserName)
ON dbo.SalesData BEFORE DELETE
WITH (STATE = ON);
GO

In this case:

  • UserA can only see rows where UserName = ‘UserA’.
  • UserB can only see rows where UserName = ‘UserB’.
  • If UserA tries to UPDATE or DELETE a row belonging to UserB, the operation is blocked with an RLS error (Msg 33504).
    Note: If you disable the Block Predicate and leave only the Filter Predicate, the same UPDATE would return (0 rows affected) without an error.
  • Similarly, if UserA tries to INSERT a row with UserName=’UserB’, it is also blocked.


    Msg 33504, Level 16, State 1, Line 3
    The attempted operation failed because the target object ‘RLS_DB.dbo.SalesData’ has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.
    The statement has been terminated.

    Pros / Cons

    • Simple and secure. No application-side control required
    • Supports both row filtering and blocking unauthorized DML (UPDATE/DELETE/INSERT)
    • Requires creating a login for each user (not practical for large-scale systems)

    Using SESSION_CONTEXT

    Another method is to use SESSION_CONTEXT.
    This feature allows you to store arbitrary key-value pairs in each session, available since SQL Server 2016.
    The application sets values such as Region=East right after login, and the RLS function uses them.

    How to Use SESSION_CONTEXT

    
-- Set a value (cannot be changed within this session)
EXEC sp_set_session_context @key = N'Region', @value = N'East', @read_only = 1;
GO

-- Retrieve the value
SELECT SESSION_CONTEXT(N'Region');
GO
    • If @read_only = 1 is specified, the key cannot be changed within this session
    • A new connection allows the value to be set again

    Implementation Example

    
USE [RLS_DB]
GO
-- Function (SESSION_CONTEXT version)
CREATE FUNCTION dbo.fn_securitypredicate_session(@Region sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS AllowAccess
WHERE @Region = CAST(SESSION_CONTEXT(N'Region') AS sysname);
GO

-- Disable the SUSER_SNAME policy (cannot apply multiple policies to the same table)
ALTER SECURITY POLICY SalesFilter_Suser WITH (STATE = OFF);
GO

-- Security policy
CREATE SECURITY POLICY SalesFilter_Session
ADD FILTER PREDICATE dbo.fn_securitypredicate_session(Region)
ON dbo.SalesData
WITH (STATE = ON);
GO

    In this case:

    • After login, if SESSION_CONTEXT('Region')='East' is set → only rows with Region = ‘East’ are visible
  • After login, if SESSION_CONTEXT('Region')='West' is set → only rows with Region = ‘West’ are visible

    Automatic Setting via Login Trigger

    Instead of letting the application set the value, SQL Server can automatically configure the session context at login.

    
USE [master]
GO
CREATE TRIGGER trg_SetRegion
ON ALL SERVER
AFTER LOGIN
AS
BEGIN
    IF ORIGINAL_LOGIN() = 'UserA'
        EXEC sp_set_session_context N'Region', N'East', @read_only = 1;
    IF ORIGINAL_LOGIN() = 'UserB'
        EXEC sp_set_session_context N'Region', N'West', @read_only = 1;
END;
GO

    This ensures the Region information is automatically set when the user logs in, preventing tampering by applications or users.

    Pros / Cons

    • Suitable for multi-tenant environments (flexible for many users)
    • If left to the application, there is a risk of tampering → use @read_only or login triggers for stronger security

    SUSER_SNAME() vs SESSION_CONTEXT

    Aspect SUSER_SNAME() SESSION_CONTEXT
    Management Method Controlled by SQL logins Set values per session via application or triggers
    Use Case Small number of users (in-house systems, etc.) Large number of users (multi-tenant SaaS, etc.)
    Security Enforced by SQL Server, strong security Depends on application, but strengthened with @read_only and login triggers
    Flexibility Low (limited to login name) High (can use Region, department, customer ID, etc.)

    Summary

    • Row-Level Security (RLS) is a mechanism to control which rows are visible to each user
    • SUSER_SNAME() is simple and secure, best for small-scale systems
    • SESSION_CONTEXT is flexible and suitable for large-scale, multi-tenant environments
    • For stronger security, use @read_only=1 and configure values with login triggers