Keeping tenant data strictly separated is a must for multi-tenant apps. Row-Level Security (RLS) in SQL Server provides a robust, enforced filter at the database level so that every query issued against protected tables automatically returns only rows the caller may see — independent of application bugs.
This article explains a practical production-ready design and implementation:
data model and schema changes
RLS predicate function and security policy (SQL Server)
how to set tenant context safely from ASP.NET Core (EF Core / ADO.NET)
admin bypass and audit scenarios
performance and testing notes
migration patterns and pitfalls
All headings are compact for easy scanning.
1. Goals and constraints
Enforce tenant isolation inside the database (single DB, multiple tenants).
Make it hard for application bugs to leak data across tenants.
Support per-tenant admins and global admins (bypass).
Allow background jobs and reporting with explicit cross-tenant permissions.
Keep low runtime overhead and maintain indexability.
2. High-level architecture (text diagram)
[Angular / API clients] --> [ASP.NET Core API] --> [SQL Server - Single DB]
| | |
| |-- sets session_context -->|
| | |
v v v
User requests Controller / Middleware RLS predicate uses
(claims include TenantId) sets SESSION_CONTEXT SESSION_CONTEXT
to filter rows by TenantId
3. Core idea (brief)
Add a TenantId column to every tenant-scoped table.
In SQL Server create an inline table-valued function (ITVF) that returns rows allowed for the current session by comparing the row TenantId with SESSION_CONTEXT('TenantId'). Optionally allow bypass if SESSION_CONTEXT('IsAdmin') = 1.
Create a security policy that attaches the predicate to the table (FILTER predicate).
Ensure every DB session used by the app has correct SESSION_CONTEXT values set immediately after opening the connection. Use an EF Core DbConnectionInterceptor (or ADO.NET wrapper) to set this per connection to work with connection pools.
For inserts where app may omit TenantId, use an AFTER INSERT trigger to set TenantId from SESSION_CONTEXT (optional).
4. Example schema and SQL scripts
Below uses uniqueidentifier for TenantId (recommended) — adapt to INT if you prefer.
4.1 Create a sample tenant table
CREATE TABLE dbo.Tenant (
TenantId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
Name NVARCHAR(200) NOT NULL
);
CREATE TABLE dbo.Customer (
CustomerId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
TenantId UNIQUEIDENTIFIER NOT NULL,
Name NVARCHAR(200) NOT NULL,
Email NVARCHAR(200) NULL,
CreatedOn DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Customer_TenantId ON dbo.Customer(TenantId);
4.2 Inline predicate function
CREATE OR ALTER FUNCTION dbo.fn_tenant_predicate(@TenantId UNIQUEIDENTIFIER)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT 1 AS allowResult
WHERE
-- If session context has IsAdmin = 1 allow everything
COALESCE(CONVERT(INT, SESSION_CONTEXT(N'IsAdmin')), 0) = 1
OR (@TenantId = CONVERT(UNIQUEIDENTIFIER, SESSION_CONTEXT(N'TenantId')))
);
GO
Notes
4.3 Apply security policy
CREATE OR ALTER SECURITY POLICY dbo.SecPolicy_Customers
ADD FILTER PREDICATE dbo.fn_tenant_predicate(TenantId) ON dbo.Customer
WITH (STATE = ON);
GO
This attaches the predicate: for every SELECT/UPDATE/DELETE the function must return a row to allow access to that row.
4.4 Optional: Insert trigger to set TenantId if omitted
CREATE OR ALTER TRIGGER dbo.trg_Customer_SetTenant
ON dbo.Customer
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE c
SET TenantId = COALESCE(c.TenantId, TRY_CONVERT(uniqueidentifier, SESSION_CONTEXT(N'TenantId')))
FROM dbo.Customer c
JOIN inserted i ON c.CustomerId = i.CustomerId;
END
GO
Be cautious: triggers increase complexity — prefer application to pass TenantId explicitly.
5. How to set session context from ASP.NET Core
RLS predicate reads SESSION_CONTEXT. Because SQL connections are pooled, set the session context each time you obtain a connection. Use an EF Core DbConnectionInterceptor (or raw ADO.NET wrapper) to set it as soon as the connection opens for use.
5.1 EF Core connection interceptor (C#)
using System.Data.Common;
using Microsoft.EntityFrameworkCore.Diagnostics;
public class TenantConnectionInterceptor : DbConnectionInterceptor
{
private readonly IHttpContextAccessor _http;
public TenantConnectionInterceptor(IHttpContextAccessor http) => _http = http;
public override async Task ConnectionOpenedAsync(DbConnection connection, ConnectionEndEventData eventData, CancellationToken cancellationToken = default)
{
await base.ConnectionOpenedAsync(connection, eventData, cancellationToken);
var httpContext = _http.HttpContext;
// If called from background worker, httpContext may be null; set TenantId accordingly
var tenantClaim = httpContext?.User?.FindFirst("tenantid")?.Value;
// Example: if the tenant id is a GUID string
if (!string.IsNullOrEmpty(tenantClaim))
{
using var cmd = connection.CreateCommand();
cmd.CommandText = "EXEC sp_set_session_context @key=N'TenantId', @value=@tid, @read_only=1;";
var p1 = cmd.CreateParameter(); p1.ParameterName = "@tid"; p1.Value = tenantClaim;
cmd.Parameters.Add(p1);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}
// If user is global admin set IsAdmin = 1
var isAdmin = httpContext?.User?.IsInRole("GlobalAdmin") == true ? 1 : 0;
using var cmd2 = connection.CreateCommand();
cmd2.CommandText = "EXEC sp_set_session_context @key=N'IsAdmin', @value=@isadmin, @read_only=1;";
var p2 = cmd2.CreateParameter(); p2.ParameterName = "@isadmin"; p2.Value = isAdmin;
cmd2.Parameters.Add(p2);
await cmd2.ExecuteNonQueryAsync(cancellationToken);
}
// Also override ConnectionOpened for sync calls if needed
}
Register interceptor in Program.cs:
builder.Services.AddHttpContextAccessor();
builder.Services.AddSingleton<DbConnectionInterceptor, TenantConnectionInterceptor>();
builder.Services.AddDbContext<MyDbContext>((sp, opts) =>
{
opts.UseSqlServer(connString);
// EF Core will pick up registered interceptors automatically
});
Notes
Use sp_set_session_context with @read_only = 1 to avoid changes later.
For background services (no HttpContext), set TenantId from worker configuration or pass tenant explicitly.
5.2 ADO.NET / Dapper approach
When you open a SqlConnection, run:
await connection.OpenAsync();
using var cmd = connection.CreateCommand();
cmd.CommandText = "EXEC sp_set_session_context @key=N'TenantId', @value=@tid, @read_only=1;";
cmd.Parameters.AddWithValue("@tid", tenantIdString);
await cmd.ExecuteNonQueryAsync();
Always set before running any queries.
6. Insert / Update / Delete considerations
INSERT: application should set TenantId on every insert. If not, consider the trigger approach above.
UPDATE / DELETE: RLS FILTER predicate works for these ops too — only rows visible to the session may be modified. That prevents cross-tenant updates.
MERGE, BULK, ADMIN: Make sure maintenance jobs run with proper admin context or use a special login that sets IsAdmin in session context.
7. Global admin & emergency cross-tenant access
To allow a global admin to see all tenants, set SESSION_CONTEXT('IsAdmin') = 1 on the connection. The predicate function checks IsAdmin first. Keep this tightly controlled and audited.
For reporting jobs that need to query all tenants, run with a service account that explicitly sets IsAdmin or uses EXECUTE AS with an elevated principal — log these operations.
8. Testing and verification
Connect as Tenant A (set TenantId session context), SELECT * FROM Customer → must only show Tenant A rows.
Connect as Tenant B → only Tenant B rows.
Connect as GlobalAdmin (IsAdmin=1) → see all rows.
Try UPDATE Customer SET Name='X' WHERE TenantId <> <current> as Tenant A → should not affect other tenant rows.
Confirm sp_set_session_context with @read_only=1 prevents later modification of the context on that session.
Helpful queries
-- show policy
SELECT * FROM sys.security_policies;
-- show predicates
SELECT * FROM sys.security_predicates;
-- show session context value
SELECT SESSION_CONTEXT(N'TenantId'), SESSION_CONTEXT(N'IsAdmin');
9. Performance and indexing
Filtering on TenantId must be efficient — create indexes that include TenantId as leading key for common predicates and joins. Example: CREATE INDEX IX_Customer_Tenant_Active ON Customer(TenantId, IsActive);
RLS predicate is applied early in plan; keep the predicate logic simple (avoid expensive scalar functions). Inline TVF is best.
Test query plans and statistics; consider partitioning very large tables by TenantId ranges when tenant data sizes are large and skewed.
10. Migration pattern for existing DB
Add TenantId column (nullable initially) to tables.
Backfill TenantId values for existing rows (requires mapping logic).
Add NOT NULL constraint (once backfilled) and add indexes.
Create predicate function and security policy in a maintenance window. Test thoroughly.
Update application code to always set TenantId in inserts and to use the interceptor.
Remove any application-level tenant filters to avoid double filtering.
11. Audit, logging and monitoring
Log every session context set (who set it, when). You can log in the interceptor before setting context.
Log global admin sessions and cross-tenant queries strongly.
Use Extended Events or Auditing for suspicious accesses.
Monitor performance metrics for RLS overhead: CPU, reads, query duration.
12. Security notes & best practices
Do not rely on application checks only. RLS enforces at DB level — that removes many classes of bugs.
Keep sp_set_session_context(..., @read_only = 1) to prevent apps from switching tenant id mid-session.
Avoid embedding tenant id in raw SQL strings; use parameters.
Strongly protect global admin credentials and rotation.
For reporting/ETL, use explicit service accounts with limited time-bound elevated privileges.
13. Troubleshooting common issues
No rows returned: check SESSION_CONTEXT('TenantId') value and type conversion. Use TRY_CONVERT.
Unexpected rows visible: ensure SESSION_CONTEXT is set on that DB connection — tools like SSMS won’t automatically set it.
Connection pooling bug: if you set context only once per connection but fail to reset it for another request that should be different, an accidental tenant leak can occur. Use @read_only=1 and always set session context on every connection open via an interceptor.
Predicate complexity: if function uses subqueries or external tables it may harm performance — keep predicate as simple as possible.
14. Example: Putting it all together (end-to-end steps)
Add TenantId to tables, index it.
Build fn_tenant_predicate to check SESSION_CONTEXT('TenantId') and IsAdmin.
Create SECURITY POLICY with FILTER predicate on each tenant scoped table.
Implement EF Core DbConnectionInterceptor to call sp_set_session_context for TenantId & IsAdmin when connection opens.
Update app to always include tenant claim in authentication token (JWT) and populate HttpContext claims.
Test as tenant, as global admin, and for background jobs.
15. Quick checklist before production
TenantId is present and indexed on every protected table.
RLS filter functions are simple and schema-bound.
Security policies are enabled and validated.
Connection interceptor or wrapper sets SESSION_CONTEXT on each connection open.
Global admin access is audited and limited.
Performance tests pass under realistic load.
Migration strategy to backfill existing rows is in place.
Monitoring & alerts configured.