Not all data should be visible to all users. In multi-user databases, Row-Level Security (RLS) ensures that users only see the data they are authorized to access—without needing complex application logic.
RLS is built into SQL Server, allowing organizations to control access dynamically based on user roles or attributes. This is essential for industries like finance, healthcare, and HR, where data privacy and compliance are critical.
In this post, we’ll explore what RLS is, how it works, and how to implement it in SQL Server and Azure SQL.
What is Row-Level Security (RLS)?
RLS is a security feature that restricts access to rows in a table based on user identity or role.
🔹 Policy-based filtering – Users see only permitted rows.
🔹 Transparent to applications – Works at the database level, no app changes required.
🔹 Performance-efficient – Enforced at query runtime.
🔹 Supports custom rules – Restrict access based on username, role, or department.
This makes RLS a powerful tool for securing multi-tenant databases, internal HR records, or financial data.
How Row-Level Security Works
RLS is implemented using Security Policies that enforce filtering logic via inline table-valued functions (TVFs).
✔️ Filter Predicate – Hides rows from unauthorized users.
✔️ Block Predicate – Prevents unauthorized modifications.
For example, in an HR database, employees should only see their own salary details, while HR managers see all records.
How to Implement Row-Level Security (RLS) in SQL Server
1️⃣ Create a User Table and Sample Data
Let’s assume we have an Employees table where we want to restrict access:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(100),
Department NVARCHAR(50),
Salary DECIMAL(10,2),
UserName NVARCHAR(100) -- Maps to system login
);
GO
INSERT INTO Employees (EmployeeID, Name, Department, Salary, UserName)
VALUES (1, 'Alice', 'HR', 75000, 'alice'),
(2, 'Bob', 'IT', 85000, 'bob'),
(3, 'Charlie', 'Finance', 95000, 'charlie');
GO
2️⃣ Create a Security Predicate Function
This function restricts access based on the current database user:
CREATE FUNCTION dbo.fn_RLS_FilterPredicate(@UserName AS NVARCHAR(100))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS AccessResult
WHERE @UserName = SYSTEM_USER; -- Use USER_NAME() for SQL Auth if SQL Auth is a requirement
GO
This means users can only see rows where UserName matches their login.
3️⃣ Apply the Security Policy
Now, enforce the rule by creating a security policy:
CREATE SECURITY POLICY EmployeeSecurityPolicy
ADD FILTER PREDICATE dbo.fn_RLS_FilterPredicate(UserName)
ON dbo.Employees
WITH (STATE = ON);
GO
Testing Row-Level Security
Now, let’s test RLS by impersonating users:
EXECUTE AS USER = 'alice';
SELECT * FROM Employees;
REVERT;
✅ Alice only sees her own record.
EXECUTE AS USER = 'bob';
SELECT * FROM Employees;
REVERT;
✅ Bob only sees his own record.
EXECUTE AS USER = 'charlie';
SELECT * FROM Employees;
REVERT;
✅ Charlie only sees his own record.
Even if an attacker gains access, they won’t see unauthorized data! 🚀
Advanced Row-Level Security Use Cases
✔️ Multi-Tenant Applications – Isolate customer data per client.
✔️ Department-Based Security – Let managers view only their department’s records.
✔️ Role-Based Security – Different access levels for HR, Finance, and IT teams.
✔️ Prevent Data Tampering – Use Block Predicates to stop unauthorized modifications.
How to Disable Row-Level Security
To remove the security policy, run:
DROP SECURITY POLICY EmployeeSecurityPolicy;
GO
DROP FUNCTION dbo.fn_RLS_FilterPredicate;
GO
RLS is now disabled, and all users can see full table data again.
Best Practices for Using Row-Level Security
✔️ Use USER_NAME() instead of SYSTEM_USER – For SQL authentication (if required).
✔️ Test with multiple roles – Ensure users see only the intended data.
✔️ Combine RLS with Column-Level Security – Restrict access at both row and column levels.
✔️ Monitor performance – While efficient, RLS can slow down queries if used on very large tables.
Conclusion
Row-Level Security (RLS) is a powerful feature that enhances database security by ensuring users can only see the data they are authorized to access.
✔️ No application changes needed
✔️ Transparent to queries
✔️ Dynamic and scalable
🔹 Have you implemented RLS in your SQL Server databases? Get started now: Microsoft Docs – Row-Level Security (RLS)
Leave a Reply