Question

You manage an Azure SQL Database for a multi-tenant SaaS application. All tenants share a single Sales table that includes a TenantId column. Application connections set the session context value 'TenantId' after connecting using a shared SQL login. You must ensure that any query issued by the application returns only rows belonging to the connecting tenant, without modifying the existing application queries. Which approach should you implement?

Accepted Answer

Create a row-level security policy with an inline table-valued function used as a filter predicate that compares TenantId to SESSION_CONTEXT(N'TenantId'), and add it to the Sales table.

Answer

Apply Dynamic Data Masking to the TenantId column so each tenant sees only masked values for other tenants.

Answer

Enable Always Encrypted on the TenantId column so the application driver decrypts only rows the tenant can access.

Answer

Create separate database schemas per tenant and grant each tenant SELECT only on its own schema.