A Multi-Tenant Schema With a TenantId Column: Keys, Indexes and Safety

Two customers can have the same local order number without sharing any data. A multi-tenant schema keeps TenantId in the row identity and relationships. Row-level security adds filtering, but the application must establish tenant context through a trusted connection path.

Put Tenant Identity in Every Multi-Tenant Schema Relationship

Shared tables store rows for several tenants. Include TenantId in each tenant-owned table and in the appropriate unique keys. A single OrderId isn't a complete identity when numbering is local to a tenant.

Foreign keys must carry TenantId too. Otherwise, an order can reference a customer in another tenant even if every individual identifier exists somewhere in the database.

I inspect foreign keys before reviewing security predicates. Isolation in SELECT doesn't repair a cross-tenant relationship already stored. The tables below use composite primary and foreign keys. Run them in a disposable database.

Their sample identities are invented input. The constraints enforce relationships independently of application filtering. A mistaken write needs rejection before a later report discovers the mismatch.

CREATE TABLE dbo.TenantCustomersDemo
(
    TenantId int NOT NULL,CustomerId int NOT NULL,CustomerName nvarchar(60) NOT NULL,
    PRIMARY KEY(TenantId,CustomerId)
);
CREATE TABLE dbo.TenantOrdersDemo
(
    TenantId int NOT NULL,OrderId int NOT NULL,CustomerId int NOT NULL,Amount decimal(19,4) NOT NULL,
    PRIMARY KEY(TenantId,OrderId),
    FOREIGN KEY(TenantId,CustomerId) REFERENCES dbo.TenantCustomersDemo(TenantId,CustomerId)
);
CREATE INDEX IX_TenantOrdersDemo_Customer
ON dbo.TenantOrdersDemo(TenantId,CustomerId) INCLUDE(Amount);
INSERT dbo.TenantCustomersDemo VALUES (1,10,N'First Tenant'),(2,10,N'Second Tenant');
INSERT dbo.TenantOrdersDemo VALUES (1,100,10,25),(2,100,10,40);

Lead Multi-Tenant Schema Indexes With TenantId

Tenant-scoped requests normally filter by TenantId before applying another business key. Put that identity first in indexes serving those requests. Include returned measures where covering the query is useful.

That organization aligns the access path with the security and lookup contract. Cross-tenant administrative reports need a separate evaluation. Don't weaken tenant lookup indexes merely to accommodate an occasional broad report.

Check the actual plan under a representative tenant. Large and small tenants can have different cardinalities and skew. A shared table doesn't imply a uniform workload.

Keep statistics and parameter behavior in the performance review. The goal is predictable tenant-scoped access with enforced identity. An index belongs to a workload, while the composite key belongs to the data's correctness contract.

Establish Session Context Through Trusted Code

SESSION_CONTEXT supplies a tenant identity to the security predicate. The application must set it from authenticated, authorized identity information. A caller allowed to send arbitrary SQL can set an arbitrary context value.

Setting the value read-only doesn't authenticate the caller. It only prevents changing that key later in the same logical session. Don't present it as protection against an untrusted direct database client.

I review connection pooling at this boundary. Every acquired connection needs the correct context before tenant queries run. A read-only key needs careful session lifecycle handling.

Test the pool's reset behavior instead of assuming it meets the requirement. Restrict table access to the approved application path. The tenant identity should come from server-side authorization, not from a form field supplied by the user.

EXEC sys.sp_set_session_context @key = N'TenantId',@value = 1;
SELECT SESSION_CONTEXT(N'TenantId') AS CurrentTenant;

Add a Filter and Write Blocks

An inline table-valued function compares each row's TenantId with the trusted session value. A missing or invalid context returns no permitted tenant match. The security policy filters reads and blocks incompatible inserts and updates.

CREATE FUNCTION needs its own batch. The following definition and policy belong to the test database. Review them under the intended deployment's ownership and permissions.

Filter predicates hide rows, while block predicates reject specified writes. Using only a filter can leave a different write behavior than the team expects. Protect each tenant-owned table, including detail tables users reach through other queries.

Policy maintenance and broad administrative access also deserve control. An administrator able to alter the policy can change the boundary, so include that role in the security design.

CREATE FUNCTION dbo.TenantAccessDemo(@TenantId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS Allowed
WHERE @TenantId = TRY_CONVERT(int,SESSION_CONTEXT(N'TenantId'));
GO
CREATE SECURITY POLICY dbo.TenantSecurityDemo
ADD FILTER PREDICATE dbo.TenantAccessDemo(TenantId) ON dbo.TenantCustomersDemo,
ADD BLOCK PREDICATE dbo.TenantAccessDemo(TenantId) ON dbo.TenantCustomersDemo AFTER INSERT,
ADD BLOCK PREDICATE dbo.TenantAccessDemo(TenantId) ON dbo.TenantCustomersDemo AFTER UPDATE,
ADD FILTER PREDICATE dbo.TenantAccessDemo(TenantId) ON dbo.TenantOrdersDemo,
ADD BLOCK PREDICATE dbo.TenantAccessDemo(TenantId) ON dbo.TenantOrdersDemo AFTER INSERT,
ADD BLOCK PREDICATE dbo.TenantAccessDemo(TenantId) ON dbo.TenantOrdersDemo AFTER UPDATE
WITH (STATE = ON);

Test Allowed and Denied Paths in a Multi-Tenant Schema

Run a read under one context and confirm only that tenant's intended rows are available. In a separate controlled test, attempt a cross-tenant write and inspect the rejection. Also test missing context.

With tenant 1 in the session context, the query below returns one row: OrderId 100 with Amount 25. The order for tenant 2 stays hidden. An insert for tenant 2 fails with error 33504, and a session without context sees no rows. A successful happy-path SELECT establishes only one part of the boundary.

What happens when a pooled connection belongs to the previous request's tenant? Make that an explicit test. Also check stored procedures, bulk paths, and background jobs. They need the same identity rule or a narrowly designed administrative path.

A shared cabinet is convenient. Giving a request the previous request's key is less convenient. The database won't infer which tenant you intended.

SELECT TenantId,OrderId,CustomerId,Amount FROM dbo.TenantOrdersDemo;

Find Legacy Relationships Before Enforcing Them

Before deploying the composite constraint to existing data, search for orders without a parent under the same TenantId. A matching CustomerId under another tenant doesn't make that row valid. Run the integrity audit through an authorized unfiltered maintenance path before enabling the policy. A tenant-filtered reader cannot certify the entire shared dataset because the policy deliberately hides other tenants from that reader.

The following shape identifies a missing intended parent and checks whether the local identity exists under another tenant. With the sample's trusted constraint, such rows shouldn't be stored. For legacy tables without that protection, resolve findings before adding the validated foreign key.

Don't silently remap the order to whichever customer shares its numeric identifier. Business identity includes the tenant and requires an approved correction.

SELECT o.TenantId,o.OrderId,o.CustomerId
FROM dbo.TenantOrdersDemo AS o
LEFT JOIN dbo.TenantCustomersDemo AS expected
  ON expected.TenantId = o.TenantId AND expected.CustomerId = o.CustomerId
WHERE expected.CustomerId IS NULL
  AND EXISTS (SELECT 1 FROM dbo.TenantCustomersDemo AS otherTenant
              WHERE otherTenant.CustomerId = o.CustomerId AND otherTenant.TenantId <> o.TenantId);

Keep Isolation in the Whole Application

Cache keys, file paths, job inputs, and exports also need tenant identity. A correct SQL predicate doesn't protect a shared application cache keyed only by OrderId. Review those boundaries with the schema.

Keep the policy definition and permission model under controlled change management. In a multi-tenant schema, the trusted application path, composite constraints, and filtering work together. None should be described as an independent substitute for the others.

Build a multi-tenant schema around complete keys, supported indexes, and a tested context lifecycle. Keep cross-tenant administrative work explicit and authorized. Review new tables for the same protection before release.

The shared storage model can serve many customers efficiently. Its safety comes from carrying identity through every relationship and request, rather than hoping every developer remembers another WHERE clause.

Related reading on this blog: Implementing Row-Level Security (RLS) and Foreign Keys Without Indexes: Finding and Fixing Slow Deletes.

Tenant isolation is not one WHERE clause, it is identity enforced across data and requests.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.