Imagine a financial firm with a single database housing information for thousands of customers. Each regional manager should only access data relevant to their territory, but instead, they see every record. This not only complicates their workflows but also poses a significant security risk. If they make a single mistake—or worse, a malicious actor compromises one account – it could expose sensitive data.

Today I want to talk about Row-Level Security, a SQL Server feature designed to enforce granular access control directly at the database level. It’s a feature I don’t normally get to play with because this type of problem usually is handled by the application rather than at the database tier. But when I told a client about this recently they got excited. It was exactly what they were after, so we moved ahead with a prototype and….I had so much fun.

Row-Level Security allows DBAs to define policies that restrict access to rows of data based on a user’s identity or role. For the financial firm, this means regional managers can only view the customer records relevant to their assigned territory, while executives might have full access. Implementing RLS ensures data remains secure, streamlined, and compliant with regulations.

Setting up RLS involves creating security policies and predicates. First, define a predicate function—typically an inline table-valued function—that determines the access logic. For example, you could create a function that checks if the user’s region matches the row’s region.

CREATE FUNCTION dbo.fnFilterByPrimaryRegion (@Region AS NVARCHAR(50))  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
RETURN SELECT 1 AS accessGranted  
WHERE @Region = SYSTEM_USER;

Next, bind this function to your table using a security policy:

CREATE SECURITY POLICY dbo.RegionSecurityPolicy  
ADD FILTER PREDICATE dbo.fnFilterByPrimaryRegion(Region)  
ON dbo.SalesData  
WITH (STATE = ON);

Once enabled, RLS automatically filters queries based on the predicate. Even if someone tries to bypass it with a direct SQL query, they’ll only see rows they’re authorized to view.

Returning to the financial firm, implementing RLS transforms how regional managers interact with the database. Each manager now has a focused view of their data, reducing confusion and minimizing the risk of exposing unrelated customer records. Executives can still access a complete dataset, and compliance audits show that sensitive data is protected by role-based policies.