Implementing Row-Level Security in SQL Server for Tenant Data Isolation

When building multi-tenant applications or systems with sensitive user data, ensuring users see only their own data is a critical security requirement. Relying on application-level logic to append WHERE clauses to every query is error-prone; a single forgotten filter can lead to a massive data leak.

Row-Level Security (RLS) solves this by moving the filtering logic into the database engine itself. By defining a security predicate, SQL Server automatically filters results based on the execution context of the user, regardless of how the query is written.

Choosing Your Isolation Strategy

Before implementing RLS, evaluate whether it is the right tool compared to traditional methods. RLS is transparent to the application, but it introduces specific architectural considerations.

Method	Maintenance	Performance	Security Robustness
Database Views	High (Manual updates)	High	Medium (Easily bypassed)
Stored Procedures	Medium	Medium (Overhead)	Medium (Encapsulated)
Row-Level Security (RLS)	Low (Set once)	Variable (Predicate cost)	High (Transparent)

Technical Constraints and Trade-offs

RLS requires a database compatibility level of 130 (SQL Server 2016) or higher. When designing predicates, keep these constraints in mind:

• Schema Binding: Predicate functions must be created with SCHEMABINDING to prevent underlying table structures from changing in ways that break the logic.
• Determinism: The function must be deterministic. It cannot reference external data or non-deterministic functions.
• Privileges: RLS does not restrict users with sysadmin or db_owner roles. It is designed for application-level users and standard database roles.
• Performance: Complex logic inside a predicate function can slow down large scans because the function is evaluated for every row in the set.

Implementation Example: Isolating Employee Data

To implement RLS, you need two components: an inline table-valued function that defines the logic, and a security policy that applies that function to a table.

Step 1: Create the Predicate Function

This function returns 1 if the user is allowed to see the row, and 0 otherwise. We use USER_NAME() to identify the current context.

-- Run in the database as an admin
CREATE FUNCTION dbo.fn_security_predicate(@EmployeeID AS int)
RETURNS TABLE
WITH SCHEMABINDING
AS
    SELECT 1 AS fn_securitypredicate_result
    -- Allow admins everything, or allow users matching their ID
    WHERE USER_NAME() = CAST(@EmployeeID AS NVARCHAR(50)) OR IS_MEMBER('dbadmin');

Step 2: Apply the Security Policy

The security policy acts as the glue, mapping the function to the target table. We use a FILTER PREDICATE to intercept SELECT operations.

CREATE SECURITY POLICY Employee_rls_policy
ADD FILTER PREDICATE dbo.fn_security_predicate(EmployeeID) ON dbo.Employees
WITH (STATE = ON);

Step 3: Add Block Predicate for Write Operations

To prevent users from inserting or updating rows they cannot see, add a BLOCK PREDICATE for AFTER INSERT, AFTER UPDATE, and BEFORE DELETE operations.

ALTER SECURITY POLICY Employee_rls_policy
ADD BLOCK PREDICATE dbo.fn_security_predicate(EmployeeID) ON dbo.Employees
AFTER INSERT, AFTER UPDATE, BEFORE DELETE;

Validation and Testing

After deployment, verify the policy behaves as expected using the following steps.

Confirm Policy Exists

SELECT name, object_id, is_enabled 
FROM sys.security_policies 
WHERE name = 'Employee_rls_policy';

Check Row Visibility for a Specific User

Use the dynamic management function to see which rows a user can access without impersonation.

SELECT * FROM sys.dm_db_row_security_allowed_to_select('dbo', 'Employees', 'john');

Execute Test Queries Under Impersonation

Run a test SELECT as a non-privileged user and confirm only permitted rows appear.

EXECUTE AS USER = 'john';
SELECT * FROM Employees;
REVERT;

Limitations and Ongoing Maintenance

• Predicate changes require ALTER: Modifying the logic means altering the function (which requires dropping the policy first if SCHEMABINDING is used) or creating a new function and updating the policy.
• Indexed views can bypass RLS: If you create indexed views on secured tables, ensure they are not used to circumvent the policy. Query hints like NOEXPAND may expose data.
• Cross-database queries: RLS predicates do not automatically apply across linked servers or cross-database queries unless the same policy exists in each database.

Practical Verification Checklist

1. Verify the predicate function has WITH SCHEMABINDING and returns a boolean-like integer (1 or 0).
2. Use sys.dm_db_row_security_allowed_to_select to check which rows a user can select.
3. Execute a query as a non-dbo user and confirm only permitted rows appear.
4. Attempt an INSERT or UPDATE as that user on a row they should not see; it should fail due to the block predicate.