Why should you care?

SaaS apps share tables but must never leak Tenant A’s invoices to Tenant B.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
IF COL_LENGTH('dbo.Orders', 'TenantId') IS NULL
    ALTER TABLE dbo.Orders ADD TenantId INT NOT NULL CONSTRAINT DF_Orders_Tenant DEFAULT (1);
CREATE OR ALTER FUNCTION dbo.fn_TenantPredicate(@TenantId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN SELECT 1 AS access_result
WHERE @TenantId = CONVERT(INT, SESSION_CONTEXT(N'TenantId'));
GO
CREATE SECURITY POLICY dbo.OrdersTenantPolicy
ADD FILTER PREDICATE dbo.fn_TenantPredicate(TenantId) ON dbo.Orders
WITH (STATE = ON);
EXEC sys.sp_set_session_context @key = N'TenantId', @value = 1;
SELECT TOP (20) OrderId, TenantId FROM dbo.Orders;

What happened?

• SESSION_CONTEXT holds the tenant id for the connection.
• The filter predicate hides other tenants’ rows automatically.

Practice next

1. Add TenantId and create the policy in lab.
2. Set session context to 1 and select.
3. Switch to tenant 2 and confirm different rows.
4. Add a BLOCK PREDICATE for writes.
5. Test db_owner behavior vs app user.

Remember

RLS enforces per-row access. Predicates use session context or user info. Perfect for multi-tenant tables.

Shared SaaS Orders table

DataVerse SaaS sets TenantId in SESSION_CONTEXT per request.

Outcome: Tenants never see each other’s orders.