How to implement Row-Level Security in SQL Server

Row-Level Security (RLS) in SQL Server lets each user see only the rows of a table that concern them, without duplicating tables or changing application code. It is a core piece of data governance, because the access rule is stored in the database itself and applies automatically to any query — whether it comes from a Power BI report, an API, or SQL Server Management Studio. In this step-by-step guide you will build an RLS policy from scratch and confirm it with different users.

Prerequisites

• SQL Server 2016 or later, or an Azure SQL Database.
• A query tool such as SQL Server Management Studio (SSMS) or Azure Data Studio.
• Permissions to create tables, functions, and security policies (for example, the db_owner role on a test database).
• Basic T-SQL knowledge, such as CREATE TABLE and SELECT.

The idea behind RLS is simple and rests on two pieces: a predicate function, which decides whether a row is visible, and a security policy, which applies that function to a table. We will build both.

Step 1: Create a sample table

We start with a sales table where each row belongs to a sales rep. The SalesRep column stores the name of the user who owns the row; that is the column the policy will use to filter.

CREATE TABLE dbo.Sales
(
    SaleId   INT IDENTITY PRIMARY KEY,
    SalesRep SYSNAME,
    Product  NVARCHAR(50),
    Amount   DECIMAL(10,2)
);

INSERT INTO dbo.Sales (SalesRep, Product, Amount) VALUES
    ('ana',   'Teclado', 120.00),
    ('bruno', 'Monitor', 240.00),
    ('ana',   'Rato',     45.00);

Step 2: Create users for testing

To see RLS in action we need different users. We create two users without login — enough for the demo — and grant them read permission on the table.

CREATE USER ana WITHOUT LOGIN;
CREATE USER bruno WITHOUT LOGIN;

GRANT SELECT ON dbo.Sales TO ana, bruno;

Step 3: Create the predicate function

The access rule lives in an inline table-valued function. It returns the value 1 when the row should appear for the user. In this example, a row is visible if the SalesRep value equals the current user's name, obtained with USER_NAME(). We store the function in its own schema, called Security, for organization and good practice.

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_vendasPorComercial(@SalesRep AS SYSNAME)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS resultado
    WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'dbo';
GO

The WITH SCHEMABINDING clause is required for functions used in RLS. The extra condition USER_NAME() = 'dbo' ensures the administrator still sees every row, which is handy for management and maintenance tasks.

Step 4: Create the security policy

All that is left is to connect the function to the table. A SECURITY POLICY with a FILTER PREDICATE applies the function to the SalesRep column of the dbo.Sales table. With STATE = ON, the policy becomes active immediately.

CREATE SECURITY POLICY Security.VendasFiltro
    ADD FILTER PREDICATE Security.fn_vendasPorComercial(SalesRep)
    ON dbo.Sales
    WITH (STATE = ON);

A FILTER PREDICATE hides rows on reads (SELECT, UPDATE, DELETE). There is also the BLOCK PREDICATE, which prevents a user from inserting or changing rows outside their scope — useful when you also want to protect writes.

Verify the result

Now we can query the table pretending to be each user, with EXECUTE AS. Sales rep "ana" should see only her two rows and "bruno" only his.

EXECUTE AS USER = 'ana';
SELECT * FROM dbo.Sales;   -- devolve as 2 linhas da ana
REVERT;

EXECUTE AS USER = 'bruno';
SELECT * FROM dbo.Sales;   -- devolve a 1 linha do bruno
REVERT;

If each user sees only their own rows, RLS is working. Notice that the query is exactly the same in both cases: the filtering happens transparently, without anyone having to add a WHERE. This transparency is what makes RLS so useful — the rule stays in the right place, the database, instead of being scattered across dozens of reports.

Conclusion

With a predicate function and a security policy you now control data access row by row, directly in SQL Server. From here you can make the rule more dynamic — for example, use SESSION_CONTEXT to identify the application user in multi-tenant scenarios — or reinforce writes with a BLOCK PREDICATE. To turn the policy off during a test, just run ALTER SECURITY POLICY Security.VendasFiltro WITH (STATE = OFF). Which other table in your model would benefit from a row-level access rule?