Row-Level Security for Reports

Two report users can run the same SELECT and need different rows. Row-level security for reports places that row filter in the database, where every supported query path meets it.

Start With the Trust Model

Row-level security, or RLS, applies a predicate function to table access. The engine filters rows according to the predicate. That can simplify reporting when many queries need the same tenant or region rule. The security boundary is only as strong as the identity value used by the predicate. A client-controlled value that any caller can change is not trustworthy by itself.

I begin with the question of who establishes the current user or tenant. The application must authenticate the person and set context through a controlled path. If the SQL login is shared, the database cannot infer the human user from the login alone. What stops one user from claiming another tenant ID? Answer that before creating the policy.

Model the Row-Level Security Filter

A common design stores a tenant or region key on each protected row and uses an inline table-valued function as a filter predicate. The function should be schema bound where required and simple enough for the optimizer to handle. Test the predicate against representative indexes and queries. Security correctness comes first, but poor design can make every report slow.

I prefer a small mapping table for complex access rules rather than embedding dozens of names in a function. The mapping has an owner, review process, and index. A region transfer then changes data, not source code. Keep the rule understandable to auditors and developers. A clever predicate nobody can explain is a maintenance risk.

Create a Simple Predicate

The following example assumes dbo.ReportData has TenantId and that a trusted application sets a TenantId value in SESSION_CONTEXT. The function returns a row only when the values match. It is a teaching example, not a complete trust design. If users can execute sp_set_session_context freely on the shared connection, they can choose another value unless the application and connection model prevent it.

I test the trust boundary before the query plan. Use separate application users and attempt to cross the tenant boundary. Confirm that missing context returns no rows. That is a safer default than returning all rows when context is absent.

CREATE FUNCTION dbo.fn_ReportTenantPredicate(@TenantId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS fn_accessResult
    WHERE @TenantId = TRY_CONVERT(int, SESSION_CONTEXT(N'TenantId'));

Attach the Row-Level Security Policy

A security policy attaches the filter predicate to the table. Once enabled, ordinary SELECT statements see only rows that pass. The table and function must exist and the executing identity must have the intended access. Test on a safe copy before enabling a policy on a busy report table. A wrong predicate can hide every row or expose more than expected.

I review the policy name and target table twice. An application query can succeed with zero rows, which is a subtle failure. Include functional tests for each role and tenant. The example assumes the function and table from the previous section already exist.

CREATE SECURITY POLICY dbo.ReportTenantPolicy
ADD FILTER PREDICATE dbo.fn_ReportTenantPredicate(TenantId)
ON dbo.ReportData
WITH (STATE = ON);

Set Context Carefully

sp_set_session_context stores a key and value for the current session. Connection pooling complicates lifecycle: a connection can be reused for a different user. Set the context at the start of every checked-out connection and clear or replace it according to the driver and application design. The read_only option can prevent changes during that logical session, but pooled connection behavior must be tested thoroughly.

I ask developers to prove that a tenant switch cannot carry the prior tenant value into the next request. A unit test that creates a fresh connection every time will miss the pooling case. Test concurrent users and failed request paths. The database filter is only as reliable as the context established before the SELECT.

EXEC sys.sp_set_session_context
    @key = N'TenantId',
    @value = 42;
SELECT SESSION_CONTEXT(N'TenantId') AS current_tenant;

Check Report and Export Paths

RLS affects queries against the protected table through the security policy, but reporting tools can have caches, extracts, or copies outside the database. Inventory those paths. A cached report generated under an elevated account can expose rows even if later interactive queries are filtered. Apply data classification and access controls to exports as well.

I test direct report views, scheduled email, data refresh, and ad hoc export. Each path can use a different login and session context. A dashboard that looks correct for one user is only one test. The security policy should be verified against every route that delivers the data.

Consider Privileged Access

Highly privileged users and policy owners need explicit test cases. Do not assume RLS automatically supplies a privileged bypass, and do not create a broad bypass without governance. If administrators require all-row access, design and audit that path deliberately. Review permissions on the predicate function, policy, and mapping table.

I ask who can change the policy and who can change the tenant mapping. Those rights can be as sensitive as direct table access. Keep changes in an approved process and monitor them. Security rules are code and data, both of which need ownership.

Measure Query Impact

A predicate can change plans and index needs. Test representative queries with the policy enabled under realistic row distributions. Watch logical reads, duration, and plan shape from your own server; do not assume one generic benchmark applies. Keep the predicate simple and indexed paths available for its join or comparison.

I compare both security and performance outcomes. A fast query that shows the wrong tenant’s rows is unacceptable. A correct policy that stalls every report also needs redesign. Measure the trade-off in a test environment, then monitor after release. Keep a rollback plan that does not expose data if the policy must be changed.

Verify the Row-Level Security Boundary Repeatedly

Build tests for matching context, missing context, wrong context, pooled connection reuse, scheduled jobs, and privileged identities. Include INSERT or UPDATE behavior if the application writes through the protected table, because filter and block predicates serve different purposes. A SELECT filter alone does not fully govern writes.

Which account can see a row it should not see? Make that a routine negative test. Row-level security is valuable because the rule travels with table access. It remains safe when identity, context, policy, and every report path are tested together.

Related reading on this blog: Implementing Row-Level Security (RLS) and Understanding Grant, Deny, and Revoke Permissions.

Row-level security is not a trusted user identity, it is a database filter that depends on one.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.