SQL Server › SQL Server Security & Permissions

How do you implement row level security to restrict which specific rows a user can see within a shared table, beyond simply controlling access to entire tables or columns?

Advanced

Answer

You create a security policy using an inline table valued function that defines the logic for which rows a given user is allowed to access, then apply that policy to the relevant table using CREATE SECURITY POLICY, so that even though every user queries the same shared table, SQL Server automatically filters the results based on the defined access rules for each individual user.

CREATE FUNCTION SecurityPredicate(@SalesRegion VARCHAR(50))
RETURNS TABLE
AS RETURN SELECT 1 AS Result
WHERE @SalesRegion = USER_NAME() OR IS_MEMBER('db_owner') = 1;

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE dbo.SecurityPredicate(SalesRegion) ON dbo.Orders;

Real-world example A multi region sales company implements row level security so that each regional sales representative querying the shared orders table automatically only sees orders belonging to their own specific region, without needing separate tables or views per region.

Common follow-ups: What is the performance impact of applying row level security to a heavily queried table?;How do database administrators typically bypass row level security when they need full visibility?

Views;Query Optimization & Plans