This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement data security and compliance
      --> Design and implement Row-Level Security (RLS)

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

What is Row-Level Security (RLS)?

Row-Level Security (RLS) is a SQL Server and Azure SQL Database feature that restricts which rows a user can access based on a security policy. Rather than controlling access to an entire table, RLS filters data so that users see only the rows they are authorized to view.

For example, a Sales table might contain data for all sales regions:

With RLS enabled:

• Alice sees only East region rows.
• Bob sees only West region rows.
• Regional managers see only their assigned regions.
• Executives may see all rows.

The application continues to query the entire table, but SQL Server automatically filters the results.

Why Use Row-Level Security?

Many organizations have users who should share the same tables while viewing different subsets of the data.

Common scenarios include:

• Multi-tenant Software-as-a-Service (SaaS) applications
• Regional sales reporting
• Department-specific HR records
• Healthcare systems where providers access only their patients
• Educational systems where instructors see only their own students
• Financial institutions with branch-specific records

Without RLS, developers often implement filtering within application code. RLS centralizes these security rules inside the database, reducing development effort and improving security.

How Row-Level Security Works

RLS works by attaching a security policy to a table.

When a query executes:

1. SQL Server identifies the current user.
2. A predicate function evaluates each row.
3. Only rows that satisfy the predicate are returned.

This occurs automatically without modifying application queries.

Row-Level Security Architecture

Application
      │
      ▼
SELECT * FROM Orders
      │
      ▼
Security Policy
      │
      ▼
Predicate Function
      │
      ▼
Only Authorized Rows Returned

The application does not need to include a WHERE clause because SQL Server applies the filtering automatically.

Components of Row-Level Security

RLS consists of three primary components:

1. Predicate Function

A predicate function determines whether a row should be visible.

Typically, this is an inline table-valued function.

Example:

CREATE FUNCTION Security.fn_FilterSales
(
    @SalesRegion NVARCHAR(50)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 1 AS fn_result
WHERE @SalesRegion = USER_NAME();

This function allows users to see rows only when the SalesRegion value matches their database user name.

2. Security Policy

The security policy associates the predicate function with a table.

Example:

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE
Security.fn_FilterSales(SalesRegion)
ON dbo.Sales
WITH (STATE = ON);

Once enabled, every query against the Sales table automatically uses the filter.

3. Protected Table

The protected table contains the actual business data.

Applications continue to issue normal SELECT, UPDATE, DELETE, and MERGE statements while SQL Server enforces the policy.

Types of Security Predicates

SQL Server supports two predicate types.

Filter Predicate

A filter predicate limits which rows users can read.

Example:

SELECT *
FROM Sales;

The query returns only rows authorized by the security policy.

This is the most commonly used predicate.

Block Predicate

A block predicate prevents unauthorized modifications.

It can prevent:

• INSERT
• UPDATE
• DELETE

Example:

A user may be allowed to read only West region rows and may also be prevented from inserting East region records.

Block Predicate Types

Block predicates can be applied:

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE

This provides fine-grained control over data modifications.

Example: Multi-Tenant Application

Imagine a SaaS application storing customer records.

Instead of creating separate databases for every customer, one database stores all tenants.

The predicate function filters rows by TenantID so that:

• TenantA users see only TenantA records.
• TenantB users see only TenantB records.

Applications require no additional filtering logic.

Example: Sales Regions

Sales table:

Logged-in user:

EastManager

Predicate:

WHERE Region = USER_NAME()

Result:

Other regions are invisible.

Creating an RLS Policy

Step 1: Create Schema

CREATE SCHEMA Security;

Step 2: Create Predicate Function

CREATE FUNCTION Security.fn_FilterRegion
(
    @Region NVARCHAR(50)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 1
WHERE @Region = USER_NAME();

Step 3: Create Security Policy

CREATE SECURITY POLICY RegionFilter
ADD FILTER PREDICATE
Security.fn_FilterRegion(Region)
ON dbo.Sales
WITH (STATE = ON);

The policy immediately begins protecting the table.

Disabling a Security Policy

ALTER SECURITY POLICY RegionFilter
WITH (STATE = OFF);

The policy remains defined but no longer filters data.

Re-enabling the Policy

ALTER SECURITY POLICY RegionFilter
WITH (STATE = ON);

Dropping a Security Policy

DROP SECURITY POLICY RegionFilter;

Security Context Functions

RLS frequently uses identity functions.

Common examples include:

These functions allow security decisions based on the current user or application context.

SESSION_CONTEXT()

Many enterprise applications use SESSION_CONTEXT() rather than database usernames.

Example:

EXEC sp_set_session_context
@key='TenantID',
@value='TenantA';

Predicate:

WHERE
@TenantID =
SESSION_CONTEXT(N'TenantID');

This approach works well in web applications where many users connect using a shared database login.

Benefits of Row-Level Security

Centralized Security

Rules exist inside the database instead of multiple applications.

Transparent to Applications

Applications issue normal SQL statements.

No code changes are typically required.

Consistent Enforcement

Every query is filtered automatically.

Developers cannot accidentally omit security filters.

Simplifies Development

No need to duplicate WHERE clauses throughout application code.

Improved Maintainability

Security policies can be updated without changing application logic.

Limitations

Not a Replacement for Authentication

Users must still authenticate.

RLS determines only which rows are visible.

Does Not Encrypt Data

Use:

• Always Encrypted
• Transparent Data Encryption (TDE)

when encryption is required.

Does Not Mask Data

Use:

• Dynamic Data Masking

when users should see masked values instead of hidden rows.

Predicate Performance

Complex predicate functions can reduce query performance.

Predicate functions should remain efficient.

RLS vs Dynamic Data Masking

RLS vs Always Encrypted

Best Practices

Keep Predicate Functions Simple

Simple predicates improve query performance.

Use SCHEMABINDING

Predicate functions should use:

WITH SCHEMABINDING

This prevents changes that could invalidate the security policy.

Use SESSION_CONTEXT() for Web Applications

This scales better than relying solely on database usernames.

Test with Non-Administrative Accounts

Database administrators often bypass normal security scenarios.

Always validate RLS using standard user accounts.

Combine with Other Security Features

For comprehensive protection, combine RLS with:

• Dynamic Data Masking
• Always Encrypted
• Transparent Data Encryption
• Microsoft Entra authentication
• Least-privilege permissions
• SQL auditing

DP-800 Exam Tips

Candidates should be able to:

• Explain the purpose of Row-Level Security.
• Differentiate filter predicates from block predicates.
• Understand the role of predicate functions and security policies.
• Create RLS using inline table-valued functions.
• Enable, disable, and drop security policies.
• Use USER_NAME(), SUSER_SNAME(), and SESSION_CONTEXT() in predicate functions.
• Differentiate RLS from Dynamic Data Masking and Always Encrypted.
• Identify common scenarios such as multi-tenant SaaS applications.
• Recognize that RLS is transparent to application code.

Practice Exam Questions

Question 1

A company stores sales records for all regions in a single table. Regional managers should view only the rows for their assigned region.

Which SQL Server feature should you implement?

A. Transparent Data Encryption

B. Row-Level Security

C. Dynamic Data Masking

D. Always Encrypted

Answer: B

Explanation: Row-Level Security filters rows based on a security policy so users automatically see only the records they are authorized to access.

Question 2

Which object determines whether a row is visible to a user in Row-Level Security?

A. Security predicate function

B. Database trigger

C. View

D. Stored procedure

Answer: A

Explanation: An inline table-valued predicate function evaluates each row and determines whether it should be returned.

Question 3

Which statement about Row-Level Security is correct?

A. It encrypts rows before storage.

B. It permanently removes unauthorized rows.

C. It automatically filters query results according to a security policy.

D. It masks sensitive column values.

Answer: C

Explanation: RLS evaluates a security policy during query execution and returns only authorized rows without modifying the stored data.

Question 4

Which type of security predicate prevents unauthorized INSERT, UPDATE, or DELETE operations?

A. Filter predicate

B. Access predicate

C. Security predicate

D. Block predicate

Answer: D

Explanation: Block predicates prevent users from performing unauthorized data modifications.

Question 5

Which function is commonly used in web applications to store tenant-specific information for Row-Level Security?

A. CURRENT_USER

B. SESSION_CONTEXT()

C. USER_ID()

D. DB_NAME()

Answer: B

Explanation: SESSION_CONTEXT() stores key-value pairs for the current session, making it ideal for multi-tenant applications.

Question 6

A developer creates the following policy:

ADD FILTER PREDICATE
Security.fn_FilterRegion(Region)
ON dbo.Sales;

What is the effect?

A. Rows are encrypted.

B. Columns are masked.

C. Unauthorized rows are automatically filtered from query results.

D. The table becomes read-only.

Answer: C

Explanation: A filter predicate restricts which rows are returned based on the predicate function.

Question 7

Which statement best describes the relationship between applications and Row-Level Security?

A. Applications must include special WHERE clauses.

B. Applications require encryption libraries.

C. Applications typically require no changes because SQL Server applies filtering automatically.

D. Applications cannot use SELECT * statements.

Answer: C

Explanation: RLS is transparent to applications. SQL Server automatically applies the filtering logic defined in the security policy.

Question 8

Which feature is most appropriate when users should see every row but sensitive values should be partially hidden?

A. Row-Level Security

B. Always Encrypted

C. Transparent Data Encryption

D. Dynamic Data Masking

Answer: D

Explanation: Dynamic Data Masking hides sensitive column values while still allowing users to access all authorized rows.

Question 9

Which statement is true regarding Row-Level Security?

A. It replaces authentication.

B. It determines which rows a user can access after authentication.

C. It encrypts the database backup.

D. It compresses tables.

Answer: B

Explanation: Authentication establishes the user’s identity, while RLS determines which rows that authenticated user is allowed to access.

Question 10

Which practice is recommended when designing Row-Level Security policies?

A. Use complex scalar functions to maximize flexibility.

B. Disable SCHEMABINDING to simplify maintenance.

C. Keep predicate functions simple and efficient to minimize performance overhead.

D. Place all filtering logic in application code instead of the database.

Answer: C

Explanation: Efficient predicate functions help reduce the performance impact of Row-Level Security while maintaining centralized, database-enforced access control.

Go to the DP-800 Exam Prep Hub main page