When an application stores data for multiple customers, keeping each customer's records separate is essential. A mistake in an API query could expose one customer's information to another, even when the application has authentication and authorization in place.

One way to add another layer of protection is to use Row-Level Security (RLS) in SQL Server. It allows the database to control which rows a user or application session can read or modify based on a defined security rule.

In this article, we will build a practical example of Row-Level Security, connect it to an ASP.NET Core application, and look at the mistakes to avoid when using it in production.

What Is Row-Level Security in SQL Server?

Row-Level Security restricts access to individual rows in a database table. Instead of relying only on application queries to filter records, you define a security policy in SQL Server.

For example, imagine an application that stores orders for three customers.

Order ID

Customer ID

Order Total

101

CUST001

250.00

102

CUST002

480.00

103

CUST003

175.00

If a session is authorized to access CUST001, the database policy can prevent that session from reading orders belonging to CUST002 or CUST003.

Without RLS, a query that accidentally omits the customer filter could return every customer's records. With a correctly configured security policy, SQL Server applies the row filter even when the query does not explicitly include CustomerId.

RLS is useful for multi-tenant applications, customer portals, financial systems, and other applications where access depends on the owner of each record.

How SQL Server Row-Level Security Works

SQL Server RLS uses two main components:

A filter predicate affects operations such as SELECT, UPDATE, and DELETE by restricting which rows the session can access. A block predicate can also prevent INSERT or UPDATE operations that would create rows outside the allowed scope.

For a basic customer-isolation example, we will use a filter predicate first.

Step 1: Create a Table for Customer Orders

Start by creating a table that associates every order with a customer.

CREATE TABLE dbo.Orders
(
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId NVARCHAR(50) NOT NULL,
    OrderDate DATETIME2 NOT NULL
        DEFAULT SYSUTCDATETIME(),
    TotalAmount DECIMAL(18,2) NOT NULL
);

Insert sample data:

INSERT INTO dbo.Orders
    (CustomerId, TotalAmount)
VALUES
    ('CUST001', 250.00),
    ('CUST002', 480.00),
    ('CUST003', 175.00);

The CustomerId column identifies the customer who owns each order. The security predicate will compare this value with the customer identity associated with the current database session.

In a real application, use a stable tenant or customer identifier. Do not base access decisions on a display name or other value that can change.

Step 2: Create a Security Predicate Function

SQL Server requires a predicate function to define the row-access rule.

For this example, the application will set a session context value named CustomerId after authenticating the user.

CREATE FUNCTION Security.fn_CustomerAccess
(
    @CustomerId NVARCHAR(50)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT 1 AS AccessGranted
    WHERE @CustomerId =
        CONVERT(
            NVARCHAR(50),
            SESSION_CONTEXT(N'CustomerId')
        )
);

The function returns a row only when the order's CustomerId matches the CustomerId stored in the current SQL Server session context.

When the values do not match, the predicate returns no row. SQL Server uses that result to restrict access.

Before creating this function, create the Security schema if it does not already exist:

CREATE SCHEMA Security;

The function does not authenticate a user or determine which customer they belong to. It only compares values. The application or another trusted database mechanism must establish the correct session context.

Step 3: Apply the Security Policy

Now connect the predicate function to the Orders table.

CREATE SECURITY POLICY Security.CustomerOrdersPolicy
ADD FILTER PREDICATE
    Security.fn_CustomerAccess(CustomerId)
    ON dbo.Orders
WITH (STATE = ON);

The policy is now active.

If the session context contains CUST001, a query against dbo.Orders will return only orders belonging to CUST001.

The same filter also restricts which rows can be targeted by operations such as UPDATE and DELETE. However, a filter predicate alone is not a complete rule for preventing unauthorized rows from being inserted.

Step 4: Test Row-Level Security

Set the customer context for the current SQL Server session:

EXEC sys.sp_set_session_context
    @key = N'CustomerId',
    @value = N'CUST001';

SELECT
    OrderId,
    CustomerId,
    TotalAmount
FROM dbo.Orders;

The query should return only rows belonging to CUST001.

Change the context to another customer:

EXEC sys.sp_set_session_context
    @key = N'CustomerId',
    @value = N'CUST002';

SELECT
    OrderId,
    CustomerId,
    TotalAmount
FROM dbo.Orders;

Now the query should return only CUST002's orders.

If the context is missing or NULL, the equality comparison does not succeed, so the predicate returns no rows. This fail-closed behavior is useful, provided the application always establishes the context correctly.

Test these cases using the same database permissions and connection behavior that the application will use. Testing only as a highly privileged administrator can produce misleading results.

Step 5: Set the Customer Context from ASP.NET Core

An ASP.NET Core application commonly uses a connection pool. Multiple HTTP requests can reuse the same physical database connection, so session context must be handled carefully.

After authenticating a request and determining the customer's identity from a trusted claim or server-side authorization process, set the context on the same open connection that will execute the database queries.

The following example uses Microsoft.Data.SqlClient:

using Microsoft.Data.SqlClient;
using System.Data;

public static async Task SetCustomerContextAsync(
    SqlConnection connection,
    string customerId,
    CancellationToken cancellationToken = default)
{
    if (string.IsNullOrWhiteSpace(customerId))
    {
        throw new ArgumentException(
            "A valid customer ID is required.",
            nameof(customerId));
    }

    await using var command = connection.CreateCommand();

    command.CommandText = """
        EXEC sys.sp_set_session_context
            @key = N'CustomerId',
            @value = @CustomerId;
        """;

    command.Parameters.Add(
        new SqlParameter("@CustomerId", SqlDbType.NVarChar, 50)
        {
            Value = customerId
        });

    await command.ExecuteNonQueryAsync(cancellationToken);
}

The caller must open the connection before calling this method. The same connection must remain open while the protected queries run.

The customer ID must come from a trusted authentication and authorization flow. Never accept a customer ID directly from a query string or request body and assume that it identifies the caller's authorized tenant.

Important considerations when using connection pooling

Session context is associated with a SQL Server session, not with an individual HTTP request. A pooled connection may be reused by a later request.

To avoid cross-customer data exposure:

For stronger isolation, consider a database access design that controls context establishment centrally, such as a carefully implemented connection interceptor or a dedicated data-access layer.

The example demonstrates the basic mechanism; production code must also handle connection opening, disposal, retries, transactions, and request cancellation correctly.

Step 6: Prevent Unauthorized Inserts and Updates

A filter predicate restricts visibility and which existing rows can be targeted, but it does not by itself prevent every invalid row from being inserted.

For example, a session associated with CUST001 should not be allowed to insert an order for CUST002.

Add a block predicate to reject such inserts and updates:

ALTER SECURITY POLICY Security.CustomerOrdersPolicy
ADD BLOCK PREDICATE
    Security.fn_CustomerAccess(CustomerId)
    ON dbo.Orders AFTER INSERT,
ADD BLOCK PREDICATE
    Security.fn_CustomerAccess(CustomerId)
    ON dbo.Orders AFTER UPDATE;

The AFTER INSERT and AFTER UPDATE predicates check the resulting row values. If the new row does not satisfy the predicate, SQL Server rejects the operation.

Test these predicates with both valid and invalid customer IDs before deploying the policy.

If your application uses administrative workflows that legitimately access multiple customers, design that access explicitly. Do not weaken tenant isolation for every application session just to support an administrative operation.

Step 7: Understand Permissions and Bypass Risks

RLS is a database security feature, but it must be combined with appropriate database permissions.

Avoid granting the application's database account unnecessary privileges. In particular, do not let ordinary application sessions modify security policies, alter predicate functions, or administer the database.

Highly privileged principals can have different access behavior. For example, members of the sysadmin role and database owners can bypass ordinary RLS restrictions. Test the policy using the actual application account, not only an administrator account.

Also remember that SQL Server RLS does not replace application authorization. The application must still verify who the user is, which operations they can perform, and whether they are permitted to access the requested business function.

Common Mistakes to Avoid

Trusting a customer ID from the request

A client can change a customer ID in a request. Establish the database context from an identity that has already been authenticated and authorized.

Forgetting to set the session context

If the context is absent, the predicate in this example returns no rows. The application should fail safely and report a configuration or authorization error rather than silently continuing with unexpected results.

Ignoring pooled connections

Connection pooling can reuse database sessions. Ensure every request sets the required context before querying protected tables.

Using only a filter predicate

A filter predicate restricts access to existing rows but does not replace block predicates for controlling which rows can be inserted or updated.

Testing only with administrator permissions

Administrative accounts may bypass RLS. Always test with the same permissions used by the application.

Assuming RLS secures every table automatically

A policy affects only the tables and operations to which it is applied. Review all tables that contain tenant-owned data, including related tables and reporting paths.

Best Practices for Production Applications

Summary

SQL Server Row-Level Security helps protect customer data by enforcing row-access rules inside the database. A predicate function defines the access condition, and a security policy applies it to a table.

In an ASP.NET Core application, session context can provide the tenant identifier used by the predicate. That context must be established from a trusted identity on every connection before protected queries execute.

For production systems, pay particular attention to connection pooling, write restrictions, database permissions, and administrative access. When configured and tested correctly, RLS provides an additional safeguard against accidental cross-tenant data exposure.