rls

To enable row-level security (RLS) in MS SQL Server (2016 and later), you need to define a security policy that uses a user-defined, inline table-valued function as a filter predicate.

Step-by-Step Guide

1. Ensure compatibility

Verify that your SQL Server instance is at least SQL Server 2016 or newer.

2. Create a schema for RLS objects (Recommended)

This practice separates your security logic from the application data.

CREATE SCHEMA Security;
GO

3. Create a predicate function

This inline table-valued function contains the logic for determining which rows a user can access. The function must be created with SCHEMABINDING.

Example: This function allows a user to see rows where the UserID column matches their username, or if they are a Manager.

CREATE FUNCTION Security.fn_securitypredicate(@UserID AS sysname)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS result
    WHERE @UserID = USER_NAME() OR USER_NAME() = 'Manager';
GO

4. Create and enable a security policy

The security policy links the predicate function to your target table and enables the RLS enforcement.

Example: This policy applies the function to the dbo.YourTable table, using the UserID column for the filter.

CREATE SECURITY POLICY SecurityPolicy
ADD FILTER PREDICATE Security.fn_securitypredicate(UserID) ON dbo.YourTable
WITH (STATE = ON);
GO

5. Grant necessary permissions

Ensure users have SELECT permission on both the target table and the security function.

GRANT SELECT ON dbo.YourTable TO SalesRep1;
GRANT SELECT ON Security.fn_securitypredicate TO SalesRep1;
-- Repeat for other users/roles as needed

Testing the Implementation
You can test RLS by impersonating different users to verify they only see authorized data. 
sql
EXECUTE AS USER = 'SalesRep1';
SELECT * FROM dbo.YourTable; -- This will only show rows matching SalesRep1's UserID

REVERT; -- Stop impersonating
EXECUTE AS USER = 'Manager';
SELECT * FROM dbo.YourTable; -- This will show all rows
REVERT