SQL Server Row-Level Security: The Filter Does Not Stop Cross-Tenant Writes

A tenant can insert a row for another tenant even when a Row-Level Security filter predicate is on. The filter hides rows from reads; it does not reject that write. Add block predicates for inserts and tenant-changing updates.

Build the Tenant Filter

This lab uses one database principal for the application and a session-context value for the tenant. The predicate returns rows whose tenant matches that value.

CREATE TABLE dbo.Orders
(
    OrderId int NOT NULL PRIMARY KEY,
    TenantId int NOT NULL,
    Note varchar(30) NOT NULL
);
INSERT dbo.Orders VALUES (1,1,'tenant one row'),(2,2,'tenant two row');

CREATE FUNCTION dbo.fn_TenantFilter(@TenantId int)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS result
WHERE @TenantId = TRY_CONVERT(int, SESSION_CONTEXT(N'TenantId'));

CREATE SECURITY POLICY dbo.TenantPolicy
ADD FILTER PREDICATE dbo.fn_TenantFilter(TenantId) ON dbo.Orders
WITH (STATE = ON);

CREATE USER TenantApp WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE ON dbo.Orders TO TenantApp;

In this test, TenantApp has only table permissions. The policy supplies the row filter.

See What the Filter Does Not Block

Run the insert as tenant 1. It succeeds even though the new row belongs to tenant 2.

EXECUTE AS USER = 'TenantApp';
EXEC sys.sp_set_session_context @key=N'TenantId', @value=1;
INSERT dbo.Orders VALUES (3,2,'cross tenant insert');
REVERT;

Switch the same database principal to tenant 2 and read the table. It can see the row tenant 1 just inserted.

EXECUTE AS USER = 'TenantApp';
EXEC sys.sp_set_session_context @key=N'TenantId', @value=2;
SELECT OrderId, TenantId, Note FROM dbo.Orders ORDER BY OrderId;
REVERT;

The database accepted the cross-tenant insert without an error. A filter predicate controls which rows a query can see; it is not a write rule.

Block Inserts and Tenant Changes

Use block predicates with the same function. The insert predicate rejects new rows for a different tenant. The update predicate stops a row being moved across tenant boundaries.

ALTER SECURITY POLICY dbo.TenantPolicy
ADD BLOCK PREDICATE dbo.fn_TenantFilter(TenantId) ON dbo.Orders AFTER INSERT,
ADD BLOCK PREDICATE dbo.fn_TenantFilter(TenantId) ON dbo.Orders AFTER UPDATE;

Retry the cross-tenant insert under tenant 1. HPAI01 rejected it with Msg 33504. The same error was returned when tenant 1 tried to change its own row to tenant 2.

Keep the Session Context Trusted

SESSION_CONTEXT is a value, not an authenticated identity. In the lab, the low-privilege TenantApp principal could set its own context to tenant 2 and read tenant 2 rows. If callers can run arbitrary SQL as a shared application principal, they can try the same. Set the tenant value in trusted application code after authentication, and do not expose that database principal for direct untrusted queries.

This pattern does not authenticate a tenant by itself. Review connection-pool reset behavior and how the application sets context before relying on it as an isolation boundary.

Frequently Asked Questions

Microsoft Docs: Row-Level Security documents filter and block predicates and execution context.