---
title: "Implement row-level security in SQL Server to restrict access to data to users."  
description: "Implement row-level security in SQL Server to restrict access to data to users."  
author: "ICSM Computer"  
published: 2024-07-11  
updated: 2024-07-12  
canonical: https://www.mindstick.com/forum/160896/implement-row-level-security-in-sql-server-to-restrict-access-to-data-to-users  
category: "SQL Server"  
tags: ["database", "sql server", "sql server 2008", "sql server 2012", "sql server 2022"]  
reading_time: 3 minutes  

---

# Implement row-level security in SQL Server to restrict access to data to users.

[Implement row-level](https://answers.mindstick.com/qa/104848/how-to-implement-row-level-security-in-oracle) [security in SQL](https://www.mindstick.com/forum/158351/how-can-you-implement-database-security-in-sql-server-and-what-are-some-best-practices) Server to restrict [access](https://www.mindstick.com/articles/12994/how-foreigners-can-access-blocked-websites-in-china) to data to users.

## Replies

### Reply by Ravi Vishwakarma

**[Row](https://www.mindstick.com/forum/1212/this-row-already-belongs-to-this-table)-level [security](https://www.mindstick.com/articles/43813/new-security-technologies) (RLS)** in [**SQL Server**](https://www.mindstick.com/articles/333990/understanding-sql-server-management-studio-ssms-and-its-role-in-microsoft-sql-server) allows you to control access to rows in a database table based on the characteristics of the user executing a query.

Here's a step-by-step guide on how to implement RLS:

#### 1. Create the Main Table

Create a table to store the data.

```plaintext
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    SaleAmount DECIMAL(10, 2),
    SalesPersonID INT,
    SaleDate DATE
);
```

#### 2. Create a Security Predicate Function

Create an inline table-valued function that returns a row if the user is authorized to view it.

```plaintext
CREATE FUNCTION dbo.SalesPersonFilter(@SalesPersonID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
WHERE @SalesPersonID = CAST(SESSION_CONTEXT(N'SalesPersonID') AS INT);
```

#### 3. Create a Security Policy

Create a security policy that binds the security predicate function to the table.

```plaintext
CREATE SECURITY POLICY SalesFilterPolicy
ADD FILTER PREDICATE dbo.SalesPersonFilter(SalesPersonID)
ON dbo.Sales
WITH (STATE = ON);
```

#### 4. Set Up User Context

Use `SESSION_CONTEXT` to set the user context when a user logs in or a session starts. This should be done by your application or within your session.

```plaintext
-- Example of setting the session context for a SalesPerson with ID 1
EXEC sp_set_session_context @key = N'SalesPersonID', @value = 1;
```

#### 5. Test the Implementation

Insert some sample data and test the row-level security.

```plaintext
-- Insert sample data
INSERT INTO Sales (SaleID, SaleAmount, SalesPersonID, SaleDate)
VALUES
(1, 100.00, 1, '2024-01-01'),
(2, 200.00, 2, '2024-01-02'),
(3, 300.00, 1, '2024-01-03');

-- Set session context for SalesPersonID 1
EXEC sp_set_session_context @key = N'SalesPersonID', @value = 1;

-- Test query as SalesPersonID 1
SELECT * FROM Sales;
```

#### Example Output:

| SaleID | SaleAmount | SalesPersonID | SaleDate |
| --- | --- | --- | --- |
| 1 | 100.0 | 1 | 2024-01-01 |
| 3 | 300.0 | 1 | 2024-01-03 |

\
In this example, the `SalesPersonID` 1 can only see their own sales data. Other sales data (e.g., `SalesPersonID` 2) are not visible.

#### Considerations

- Ensure that the security policy and predicate function are created with `SCHEMABINDING`.
- The application must set them `SESSION_CONTEXT` correctly based on the authenticated user.
- Review and test the security policies thoroughly to ensure they meet the security requirements.
- Use database roles and permissions to further secure the implementation.

By implementing RLS, you can effectively control access to rows in a table, ensuring that users only see the data they're authorized to view.

## Read more

[**Write a basic SELECT statement to retrieve data from a SQL Server table.**](https://www.mindstick.com/interview/33935/write-a-basic-select-statement-to-retrieve-data-from-a-sql-server-table)

[**write a query to n-th highest salary.**](https://www.mindstick.com/interview/33937/write-a-query-to-n-th-highest-salary)


---

Original Source: https://www.mindstick.com/forum/160896/implement-row-level-security-in-sql-server-to-restrict-access-to-data-to-users

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.