Row-Level Security in SQL Server: Closing the Gap Application Code Can Miss

Lesson 1’s roles control access at the table level — “can this user query dbo.Order at all.” Row-Level Security (RLS) goes one level finer: which rows within a table a given user can see, enforced by the engine itself rather than trusted entirely to application-layer WHERE clauses. For multi-tenant applications, this closes a genuinely common, genuinely serious real-world bug class.

A Basic Security Predicate

CREATE FUNCTION dbo.fn_SecurityPredicate (@region NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result WHERE @region = USER_NAME() OR IS_MEMBER('db_owner') = 1;

CREATE SECURITY POLICY OrderRegionPolicy
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(region) ON dbo.SomeTable
WITH (STATE = ON);

Once this policy is active, SELECT * FROM dbo.SomeTable run by a non-admin user automatically only returns rows matching their region — with no WHERE clause required in the calling code at all. The filter applies transparently to every query against the table, including ones written by developers who don’t even know RLS exists on it.

Why This Matters in Practice

This is the exact same category of reasoning as constraints vs. application-only validation (Chapter 5): trusting every single query, in every report, written by every developer who ever touches the codebase, to correctly filter by tenant is a much weaker guarantee than the engine enforcing it structurally. One forgotten WHERE tenant_id = @currentTenant in a hastily-written admin report is a real, documented category of data breach — RLS makes that specific mistake impossible rather than merely unlikely.

Auditing Existing Permissions

SELECT dp.name AS principal_name, dp.type_desc, o.permission_name, o.state_desc
FROM sys.database_permissions o
JOIN sys.database_principals dp ON dp.principal_id = o.grantee_principal_id
WHERE o.major_id = OBJECT_ID('dbo.OrderLog');

A quick, worthwhile habit before granting anything new: check what’s already granted on a sensitive table, so you’re not stacking overlapping permissions you can’t easily reason about later. Combined with Lesson 1’s DENY-always-wins rule, a table can accumulate a genuinely confusing tangle of GRANTs and DENYs across multiple roles over time — this query is how you actually see the full picture before adding to it.