Row-Level Security in Azure SQL

CREATE SCHEMA rls;
GO

Step 2: Create an inline table-valued function that defines the filter logic.

CREATE FUNCTION rls.fn_salesFilter(@SalesRepID AS INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
WHERE @SalesRepID = CAST(SESSION_CONTEXT(N'SalesRepID') AS INT)
   OR CAST(SESSION_CONTEXT(N'Role') AS NVARCHAR(50)) = 'Manager';
GO

Step 3: Create the security policy that binds the function to the table.

CREATE SECURITY POLICY rls.salesPolicy
ADD FILTER PREDICATE rls.fn_salesFilter(SalesRepID)
ON dbo.Sales
WITH (STATE = ON);
GO

Step 4: Set the session context in the application before executing queries.

EXEC sp_set_session_context @key = N'SalesRepID', @value = 123;
EXEC sp_set_session_context @key = N'Role', @value = 'SalesRep';

SELECT * FROM sys.security_policies;
SELECT * FROM sys.security_predicates;

To disable or drop a policy:

ALTER SECURITY POLICY rls.salesPolicy WITH (STATE = OFF);
DROP SECURITY POLICY rls.salesPolicy;

In a Software-as-a-Service (SaaS) application serving multiple companies (tenants) from a single Azure SQL Database, RLS is a cornerstone of data isolation. For example, a CRM platform stores all customer records in one table with a TenantID column. Without RLS, every query would require a WHERE TenantID = @tenantID clause, which is error-prone and hard to enforce consistently. By implementing RLS with a predicate that checks SESSION_CONTEXT('TenantID'), the application sets the tenant ID once per session, and all subsequent queries automatically filter rows. This reduces code complexity and ensures no data leaks even if a developer forgets to add the WHERE clause.

Another scenario is a financial reporting system where managers should see only their direct reports' data. The database has an Employees table with a ManagerID column. The RLS predicate uses the current user's EmployeeID (obtained from SESSION_CONTEXT('EmployeeID')) to filter rows where ManagerID equals that ID. This allows the application to reuse the same queries for all managers without modification.

In production, performance considerations are important. For a table with millions of rows, the predicate column (e.g., TenantID) should be indexed. Without an index, RLS causes a full table scan for every query, leading to performance degradation. Additionally, when using SESSION_CONTEXT, the application must set the context immediately after opening the connection, before any queries. Misconfiguration, such as forgetting to set the context, results in users seeing no data (if the predicate returns false) or all data (if the predicate is not applied due to an error). Common pitfalls include using USER_NAME() instead of SESSION_CONTEXT(), which is less secure because the username can be spoofed if the application uses a single database login. Also, failing to schema-bind the predicate function causes an error when creating the policy.