Implementing Row-Level Security in a Fabric Warehouse and Lakehouse SQL Analytics Endpoint: Dynamic Policies, Workspace Roles, and Testing Access as a Business User

Introduction

Imagine your company operates across four sales regions — Northeast, Southeast, Midwest, and West. Your data warehouse holds every transaction from every region in a single sales.Orders table. The regional VP of Sales for the Midwest has just been granted access to the Fabric workspace, and she can now query the full table. Every row. Including the numbers her counterparts in the other regions are posting. That's not a data governance gap — it's a liability.

Row-Level Security (RLS) is the mechanism that closes it. Rather than creating separate filtered views or maintaining four separate copies of the data — each a maintenance headache and a consistency risk — you define a policy that filters rows invisibly, at the engine level, based on who is running the query. The Midwest VP runs the exact same query as the West VP, sees the exact same columns, but the engine returns only the rows that belong to them. No special views required. No data duplication.

By the end of this lesson, you will be able to implement production-ready RLS on both a Fabric Warehouse and a Lakehouse SQL Analytics Endpoint, understand the meaningful architectural differences between the two, and test your policies rigorously — including impersonating a business user to verify that the policy behaves exactly as intended before it ever touches a real user's session.

What you'll learn:

• How T-SQL CREATE SECURITY POLICY works in a Fabric Warehouse, and the exact syntax differences from Azure SQL Database
• How to build a user mapping table and a dynamic inline table-valued function that drives access control
• How to apply and verify RLS on a Lakehouse SQL Analytics Endpoint and understand its current limitations
• How workspace roles interact with (and can undermine) RLS policies
• How to test access as a business user using EXECUTE AS USER and external user impersonation

---

Prerequisites

You should already be comfortable with:

• The difference between a Fabric Warehouse and a Lakehouse SQL Analytics Endpoint — if you need a refresher, Fabric Lakehouse vs Warehouse: Choosing the Right Store for Your Workload covers the architecture clearly
• Basic T-SQL DDL: CREATE TABLE, CREATE VIEW, CREATE FUNCTION, CREATE SCHEMA
• Fabric workspace roles (Admin, Member, Contributor, Viewer) — these interact with RLS in ways that will surprise you if you haven't encountered them before; Securing and Governing Microsoft Fabric: Workspace Roles, Item Permissions, and OneLake Data Access is essential context
• A Fabric capacity (F4 or higher recommended for following along without throttling). Trial capacities work. See Fabric Capacities and Workspaces: F SKUs, Trials, and Setting Up Your First Workspace if you need to stand one up

You do not need to be a T-SQL security expert, but you should understand the basics of schemas, functions, and what a predicate is.

---

Understanding Row-Level Security at the Engine Level

Before you write a single line of T-SQL, you need a clear mental model of how RLS actually works. Misunderstanding this leads to one of two failure modes: a policy that's too permissive (users see rows they shouldn't), or a policy that's too restrictive (users see nothing, or the wrong thing, and blame the data team).

RLS in SQL Server, Azure SQL, and Fabric Warehouse is implemented through security predicates. A security predicate is a function that takes a row as input and returns 1 (visible) or 0 (invisible). That function gets called for every row the engine would otherwise return. You wire the predicate to a table via a security policy object.

There are two types of predicates:

• Filter predicates — silently remove rows that the current user isn't authorized to see. The user gets fewer rows. No error.
• Block predicates — prevent INSERT, UPDATE, and DELETE operations on rows the user doesn't own. You can block operations before or after the change is evaluated.

For most analytical use cases — regional sales data, department financials, patient records — you want filter predicates. You don't usually need block predicates on a warehouse because business users are reading, not writing.

Here's the architecture of what you're building:

[Query from User A]
        ↓
[SQL Engine evaluates SELECT against sales.Orders]
        ↓
[For each candidate row, engine calls fn_SecurityPredicate(Region)]
        ↓
[Function checks: does User A map to this Region in dbo.UserRegionMap?]
        ↓
[Returns only rows where function returns 1]
        ↓
[User A sees their data. No error. No hint about other rows.]

The function is the brain. The policy is the wiring. The mapping table is the data.

---

Setting Up the Environment

We're going to build a realistic scenario: a retail company with sales data segmented by region. Users are regional sales managers and a national account executive who needs full visibility.

Creating the Schema and Tables

Open the SQL query editor in your Fabric Warehouse. We'll create everything in a dedicated schema to keep things organized.

-- Create schemas
CREATE SCHEMA sales;
CREATE SCHEMA security;
GO

-- Main fact table
CREATE TABLE sales.Orders (
    OrderID         INT             NOT NULL,
    CustomerID      INT             NOT NULL,
    Region          VARCHAR(50)     NOT NULL,
    SalesRepEmail   VARCHAR(255)    NOT NULL,
    OrderDate       DATE            NOT NULL,
    OrderAmount     DECIMAL(12, 2)  NOT NULL,
    ProductCategory VARCHAR(100)    NOT NULL
);
GO

-- Load some realistic test data
INSERT INTO sales.Orders (OrderID, CustomerID, Region, SalesRepEmail, OrderDate, OrderAmount, ProductCategory)
VALUES
    (1001, 5001, 'Northeast', 'alice@contoso.com',   '2024-01-15', 12500.00, 'Enterprise Software'),
    (1002, 5002, 'Northeast', 'alice@contoso.com',   '2024-01-22', 8750.50,  'Professional Services'),
    (1003, 5003, 'Midwest',   'carlos@contoso.com',  '2024-01-18', 22000.00, 'Enterprise Software'),
    (1004, 5004, 'Midwest',   'carlos@contoso.com',  '2024-01-29', 5400.00,  'Hardware'),
    (1005, 5005, 'Southeast', 'diana@contoso.com',   '2024-02-03', 17800.00, 'Enterprise Software'),
    (1006, 5006, 'West',      'ethan@contoso.com',   '2024-02-10', 31000.00, 'Cloud Infrastructure'),
    (1007, 5007, 'West',      'ethan@contoso.com',   '2024-02-14', 9200.00,  'Professional Services'),
    (1008, 5008, 'Northeast', 'alice@contoso.com',   '2024-02-20', 14300.00, 'Hardware'),
    (1009, 5009, 'Midwest',   'carlos@contoso.com',  '2024-03-05', 6700.00,  'Cloud Infrastructure'),
    (1010, 5010, 'Southeast', 'diana@contoso.com',   '2024-03-12', 19500.00, 'Professional Services');
GO

Creating the User-to-Region Mapping Table

The mapping table is the core of a dynamic policy — one that doesn't hardcode user names into the function itself. Instead, you maintain a table that maps usernames (specifically, the value that USER_NAME() or SYSTEM_USER returns in the session) to the regions they're allowed to see.

CREATE TABLE security.UserRegionMap (
    UserPrincipalName   VARCHAR(255)    NOT NULL,
    Region              VARCHAR(50)     NOT NULL,
    CONSTRAINT PK_UserRegionMap PRIMARY KEY (UserPrincipalName, Region)
);
GO

-- Map users to their regions
-- Note: in Fabric, USER_NAME() returns the Entra ID UPN (email address)
INSERT INTO security.UserRegionMap (UserPrincipalName, Region)
VALUES
    ('alice@contoso.com',   'Northeast'),
    ('carlos@contoso.com',  'Midwest'),
    ('diana@contoso.com',   'Southeast'),
    ('ethan@contoso.com',   'West'),
    -- National account exec sees everything -- we'll add all four regions
    ('national@contoso.com', 'Northeast'),
    ('national@contoso.com', 'Southeast'),
    ('national@contoso.com', 'Midwest'),
    ('national@contoso.com', 'West');
GO

---

Building the Security Predicate Function

The inline table-valued function (iTVF) is the heart of the policy. You need to understand why an inline TVF specifically — not a scalar function, not a multi-statement TVF.

Inline TVFs are transparent to the query optimizer. The engine can fold the function's logic into the outer query plan, which means it can push predicates and use indexes effectively. Scalar functions and multi-statement TVFs are black boxes from the optimizer's perspective, which causes catastrophic performance degradation — the function gets called once per row, the optimizer can't reason about it, and indexes become useless. Always use an inline TVF for your security predicate.

CREATE FUNCTION security.fn_OrdersRegionPredicate
(
    @Region VARCHAR(50)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS fn_SecurityResult
    FROM security.UserRegionMap AS urm
    WHERE urm.Region = @Region
      AND urm.UserPrincipalName = USER_NAME();
GO

Let's dissect this carefully:

• The function takes @Region — this is the column value from the row being evaluated
• It queries UserRegionMap to see whether the current user (USER_NAME()) has an entry matching that region
• If the query returns a row, the row is visible (the predicate returns a result). If not, the row is filtered out.
• WITH SCHEMABINDING is required for security predicates in Fabric Warehouse — it prevents the underlying tables from being altered in ways that would silently break the function

Test the function in isolation before creating the policy:

-- Simulate what the function returns for the current user and a given region
SELECT *
FROM security.fn_OrdersRegionPredicate('Midwest');

-- If you're logged in as carlos@contoso.com, this should return one row.
-- If you're logged in as alice@contoso.com, this should return nothing.

---

Creating and Enabling the Security Policy

Now you wire the predicate to the table:

CREATE SECURITY POLICY security.OrdersRegionPolicy
    ADD FILTER PREDICATE security.fn_OrdersRegionPredicate(Region)
    ON sales.Orders
    WITH (STATE = ON);
GO

The policy is now active. Any query against sales.Orders will be automatically filtered by the predicate — for every user, including workspace admins querying through the SQL endpoint, unless they're connecting with a service principal that has special privileges (we'll come back to that).

Verify the policy was created:

SELECT 
    pol.name                AS PolicyName,
    pol.is_enabled,
    pol.type_desc,
    pred.predicate_type_desc,
    OBJECT_NAME(pred.target_object_id) AS TargetTable,
    pred.predicate_definition
FROM sys.security_policies AS pol
JOIN sys.security_predicates AS pred
    ON pol.object_id = pred.object_id;

You should see your OrdersRegionPolicy listed with FILTER as the predicate type.

---

The Admin Override Pattern

Here's a subtlety that catches teams off guard: once you enable a security policy, it applies to everyone — including workspace admins querying through the SQL endpoint. If the admin isn't in the UserRegionMap table, they'll see zero rows.

You have two legitimate approaches to handle admin access:

Option 1: Add Admins to the Mapping Table

The simplest approach. Add each admin or service account to the mapping table for all regions. The downside is that you need to maintain this list, and if someone's role changes and they're removed from the workspace, you also need to remove them from the mapping table.

Option 2: IS_ROLEMEMBER Check in the Predicate

You can check for a specific database role membership inside the predicate function:

-- First, create an exemption role
CREATE ROLE rls_bypass;
GO

-- Modify the function to allow role members through
ALTER FUNCTION security.fn_OrdersRegionPredicate
(
    @Region VARCHAR(50)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS fn_SecurityResult
    WHERE IS_ROLEMEMBER('rls_bypass') = 1  -- Bypass for admins

    UNION ALL

    SELECT 1 AS fn_SecurityResult
    FROM security.UserRegionMap AS urm
    WHERE urm.Region = @Region
      AND urm.UserPrincipalName = USER_NAME()
      AND IS_ROLEMEMBER('rls_bypass') = 0;  -- Normal users
GO

Then grant the bypass role to specific users:

-- In Fabric Warehouse, users must first exist as database users
-- This typically happens automatically when they access the warehouse
ALTER ROLE rls_bypass ADD MEMBER [admin@contoso.com];

---

Handling RLS on the Lakehouse SQL Analytics Endpoint

The Lakehouse SQL Analytics Endpoint is where the story gets meaningfully different — and where many teams discover, after the fact, that their security assumptions were wrong.

The SQL Analytics Endpoint lets you query Delta tables in a lakehouse using T-SQL, as covered in Querying Lakehouse Data with the SQL Analytics Endpoint: Writing T-SQL Against Delta Tables Without a Warehouse. It presents a SQL interface over the Delta tables that live in OneLake. But the security model has important constraints.

What the SQL Analytics Endpoint Supports

As of current Fabric releases, the SQL Analytics Endpoint does support RLS via the same CREATE SECURITY POLICY mechanism. You can create filter predicates on tables that the endpoint exposes. The syntax is identical to the warehouse.

Here's how you'd apply the same pattern to a lakehouse endpoint:

-- In the SQL Analytics Endpoint query editor

-- Create the mapping table
-- Note: This creates a managed table in the lakehouse, which becomes a Delta table in OneLake
CREATE TABLE security_UserRegionMap (
    UserPrincipalName   VARCHAR(255)    NOT NULL,
    Region              VARCHAR(50)     NOT NULL
);

INSERT INTO security_UserRegionMap VALUES
    ('alice@contoso.com',   'Northeast'),
    ('carlos@contoso.com',  'Midwest'),
    ('diana@contoso.com',   'Southeast'),
    ('ethan@contoso.com',   'West');

-- Create the predicate function
CREATE FUNCTION fn_LakehouseOrdersPredicate
(
    @Region VARCHAR(50)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS fn_SecurityResult
    FROM dbo.security_UserRegionMap AS urm
    WHERE urm.Region = @Region
      AND urm.UserPrincipalName = USER_NAME();

-- Apply the policy
CREATE SECURITY POLICY LakehouseOrdersPolicy
    ADD FILTER PREDICATE fn_LakehouseOrdersPredicate(Region)
    ON dbo.orders
    WITH (STATE = ON);

Critical Limitations You Must Understand

Limitation 1: Schemas are flat in the SQL Analytics Endpoint. Unlike the warehouse, the SQL Analytics Endpoint uses dbo as its default schema and doesn't support creating custom schemas for organization. This means you can't use the security. schema prefix convention — everything goes into dbo. Plan your naming conventions accordingly.

Limitation 2: The endpoint is read-only for Delta tables synchronized from the lakehouse. You can't create RLS on tables that were created by Spark or Dataflow Gen2 and then auto-discovered by the endpoint — you can only query those. RLS applies to tables that are managed through the SQL endpoint itself. This is a subtle but important distinction.

Limitation 3: Direct OneLake access bypasses the SQL endpoint entirely. If a user has access to the underlying OneLake storage (through the Files API, through a Spark notebook, or through a shortcut), the SQL endpoint's RLS policies are completely irrelevant. RLS only governs access through the SQL interface. This is not a quirk — it's by design — but it's a critical gap if you're relying on the endpoint for data governance.

Limitation 4: RLS in the SQL Analytics Endpoint does not propagate to Power BI semantic models built on top of it. When Power BI queries the endpoint, it uses the service account identity for Direct Lake connections, not the individual user's identity. Your SQL-level RLS won't filter rows in Power BI reports unless you also implement RLS in the Power BI semantic model itself.

---

How Workspace Roles Interact with RLS

This is where the vast majority of security implementations go wrong. Here's the critical rule:

Workspace roles grant access to the workspace and its items. RLS controls what data within those items a user can see. These are two independent layers, and one does not substitute for the other.

Consider what happens when you grant someone the Viewer workspace role. They can see the warehouse exists, open the SQL editor, and run queries. If you have RLS enabled, they'll see filtered data based on their identity. Good so far.

But consider the Member and Contributor roles. Users in these roles have ALTER permissions on objects within the workspace. A Contributor can potentially:

• Alter the schema of a table that the predicate function is bound to (which fails if you used WITH SCHEMABINDING, but only if you used it)
• Drop and recreate a view that sits on top of the secured table
• Create new tables or views that join to the secured table in ways the policy doesn't govern

Here's a concrete example of the problem. Suppose Alice has the Contributor role (so she can build reports) but should only see the Northeast region. She runs:

-- Alice can create a view that cross-joins the secured table with itself
-- The view inherits RLS, so this particular attack doesn't work
-- But she could run aggregations that reveal regional totals through inference:

SELECT ProductCategory, COUNT(*) as OrderCount, SUM(OrderAmount) as TotalAmount
FROM sales.Orders
GROUP BY ProductCategory;
-- This returns ONLY her region's rows because RLS is applied before GROUP BY
-- The aggregated numbers don't reveal individual rows but might reveal more than intended

The view-based attack actually doesn't work because RLS applies underneath views — views inherit the security policy. But aggregations on filtered data can still leak information if the data cardinality is low enough. This is a data inference problem, not an RLS bypass, but it's worth understanding.

The Right Access Architecture

Here's the layered access model you should implement:

Layer 1: Entra ID Group Membership
    → Who can access the workspace at all?
    → Managed in Azure Active Directory / Entra ID

Layer 2: Workspace Role (Viewer for most business users)
    → Can they read but not modify workspace artifacts?
    → Managed in Fabric workspace settings

Layer 3: Item-Level Permissions (Read on specific warehouses/lakehouses)
    → Can they connect to this specific data store?
    → Managed via "Manage permissions" on the item

Layer 4: RLS Policy (Dynamic row filtering)
    → Which rows can they see within the tables they can query?
    → Managed via T-SQL security policies

Each layer is a gate. You want users passing through all four layers, with each layer enforcing its specific constraint.

---

Testing Access as a Business User

This is where most implementations reveal their flaws. You've written the policy, checked the sys.security_policies catalog, and called it done. But you've only tested as yourself — an admin who can see everything. The real test is verifying the policy from the perspective of a user with restricted access.

Method 1: EXECUTE AS USER (In-Session Impersonation)

The EXECUTE AS USER statement lets you temporarily assume another user's identity within your current session. This is the fastest way to verify policy behavior without actually logging in as another user.

-- First, verify current user sees all rows (assumes admin is in bypass role or all regions)
SELECT Region, COUNT(*) as RowCount
FROM sales.Orders
GROUP BY Region;
-- Expected: Northeast: 3, Midwest: 3, Southeast: 2, West: 2

-- Now impersonate Carlos (Midwest only)
EXECUTE AS USER = 'carlos@contoso.com';

SELECT USER_NAME(); -- Should return 'carlos@contoso.com'

SELECT Region, COUNT(*) as RowCount
FROM sales.Orders
GROUP BY Region;
-- Expected: Midwest: 3 (only Carlos's rows)

SELECT * FROM sales.Orders;
-- Expected: only the 3 Midwest rows

-- Try to access data from another region directly
SELECT * FROM sales.Orders WHERE Region = 'Northeast';
-- Expected: 0 rows returned (not an error — RLS silently filters)

-- Return to original identity
REVERT;

SELECT USER_NAME(); -- Should return your original UPN

Method 2: Testing the Predicate Function Directly

Before you even create the policy, test the function with simulated identity:

-- Test the function as if you were Carlos
EXECUTE AS USER = 'carlos@contoso.com';

-- The function should return a row for Midwest but not for Northeast
SELECT 'Midwest' as TestRegion, COUNT(*) as ShouldBeOne 
FROM security.fn_OrdersRegionPredicate('Midwest');

SELECT 'Northeast' as TestRegion, COUNT(*) as ShouldBeZero
FROM security.fn_OrdersRegionPredicate('Northeast');

SELECT 'West' as TestRegion, COUNT(*) as ShouldBeZero
FROM security.fn_OrdersRegionPredicate('West');

REVERT;

This isolates the predicate logic from the full policy, making it easy to debug whether failures are in the function or in the policy wiring.

Method 3: External User Testing (The Gold Standard)

EXECUTE AS USER is convenient, but it's a simulation. The true validation is to have an actual restricted user log in and run queries. This catches problems that impersonation can miss:

• Token-based authentication differences between impersonation and real login
• Multi-factor authentication flows that might affect identity resolution
• The user's actual client (Power BI Desktop, Excel, SSMS, the Fabric web editor)

Here's how to structure external user testing systematically:

Step 1: Create a test user in your Entra ID tenant. Name them something obvious like rls-test-northeast@yourtenantdomain.com. Add them to the UserRegionMap for Northeast only.

Step 2: Grant the user Viewer access to the workspace. Do this through workspace settings, not by making them an admin.

Step 3: Grant the user Read permission on the specific warehouse item. In the workspace, find the warehouse, select "Manage permissions," and add the test user with Read permissions (which maps to CONNECT on the database).

Step 4: Have the test user (or you, logged in a separate browser session in InPrivate/Incognito mode) execute your validation script:

-- Test script to be run by the restricted user
SELECT USER_NAME() AS MyIdentity;

-- Should return only Northeast rows
SELECT * FROM sales.Orders;

-- Count by region - should only show 'Northeast'
SELECT Region, COUNT(*) as OrderCount
FROM sales.Orders
GROUP BY Region;

-- Try an explicit cross-region filter - should return 0 rows, not an error
SELECT * FROM sales.Orders WHERE Region = 'Midwest';

-- Aggregate query - should reflect only Northeast totals
SELECT 
    SUM(OrderAmount) AS TotalRevenue,
    AVG(OrderAmount) AS AvgOrderSize
FROM sales.Orders;

Step 5: Document the expected and actual outputs for each query. This becomes your RLS test suite — run it every time you modify the policy, add a new predicate, or change the mapping table.

---

Handling Multi-Column RLS and Compound Predicates

Real-world scenarios often require more nuanced filtering than a single column. A common pattern: filter by region AND by whether the user is the sales rep on the order.

-- More complex mapping table
CREATE TABLE security.UserAccessMap (
    UserPrincipalName   VARCHAR(255)    NOT NULL,
    Region              VARCHAR(50)     NULL,       -- NULL means all regions
    SalesRepEmail       VARCHAR(255)    NULL,       -- NULL means all reps
    AccessLevel         VARCHAR(20)     NOT NULL,   -- 'regional', 'rep', 'global'
    CONSTRAINT PK_UserAccessMap PRIMARY KEY (UserPrincipalName, AccessLevel)
);
GO

INSERT INTO security.UserAccessMap VALUES
    ('alice@contoso.com',    'Northeast', 'alice@contoso.com', 'rep'),
    ('mgr.northeast@c.com',  'Northeast', NULL,               'regional'),
    ('national@contoso.com', NULL,        NULL,               'global');
GO

-- Compound predicate function
CREATE FUNCTION security.fn_OrdersCompoundPredicate
(
    @Region         VARCHAR(50),
    @SalesRepEmail  VARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS fn_SecurityResult
    FROM security.UserAccessMap AS uam
    WHERE uam.UserPrincipalName = USER_NAME()
      AND (
            uam.AccessLevel = 'global'                              -- Global access
            OR (uam.AccessLevel = 'regional' AND uam.Region = @Region) -- Regional filter
            OR (uam.AccessLevel = 'rep' AND uam.SalesRepEmail = @SalesRepEmail) -- Own rows only
          );
GO

-- Wire the compound predicate to the table
-- First disable the existing policy
ALTER SECURITY POLICY security.OrdersRegionPolicy WITH (STATE = OFF);
DROP SECURITY POLICY security.OrdersRegionPolicy;
DROP FUNCTION security.fn_OrdersRegionPredicate;

CREATE SECURITY POLICY security.OrdersPolicy
    ADD FILTER PREDICATE security.fn_OrdersCompoundPredicate(Region, SalesRepEmail)
    ON sales.Orders
    WITH (STATE = ON);
GO

Test the compound predicate carefully:

-- Test as Alice (rep-level access, Northeast only, own rows only)
EXECUTE AS USER = 'alice@contoso.com';
SELECT OrderID, Region, SalesRepEmail, OrderAmount FROM sales.Orders;
-- Should return only rows where SalesRepEmail = 'alice@contoso.com'
REVERT;

-- Test as Northeast manager (regional access, all Northeast rows)
EXECUTE AS USER = 'mgr.northeast@c.com';
SELECT OrderID, Region, SalesRepEmail, OrderAmount FROM sales.Orders;
-- Should return all Northeast rows regardless of sales rep
REVERT;

-- Test as national exec (global access, all rows)
EXECUTE AS USER = 'national@contoso.com';
SELECT OrderID, Region, SalesRepEmail, OrderAmount FROM sales.Orders;
-- Should return all rows
REVERT;

---

Performance Considerations and Common Pitfalls

The N+1 Problem with Inline TVFs

Even with inline TVFs, a predicate that can't leverage indexes will cause full table scans on your mapping table for every row evaluation. Make sure your mapping table is properly indexed:

-- Index to support the most common lookup pattern
CREATE INDEX IX_UserRegionMap_UPN_Region 
ON security.UserRegionMap (UserPrincipalName, Region);

For very large fact tables (hundreds of millions of rows), test your query plans with RLS enabled versus disabled. Use SET STATISTICS IO ON to compare logical reads. If the plan shows a significant increase in reads with RLS enabled, your predicate function may be preventing index seeks on the fact table itself.

The Columnar Storage Consideration

Fabric Warehouse uses V-Order and columnar storage under the hood. RLS filter predicates interact with columnar storage differently than row-store indexes. The engine may read entire row groups and then apply the predicate in memory, rather than skipping row groups based on statistics. This means RLS overhead on a warehouse with very large tables can be non-trivial. Always benchmark with realistic data volumes.

The View Layer Anti-Pattern

Some teams create views that bake the RLS logic in:

-- Anti-pattern: Don't do this
CREATE VIEW sales.Northeast_Orders AS
SELECT * FROM sales.Orders WHERE Region = 'Northeast';

This seems to work but creates an unmaintainable mess:

• Each user needs their own view or a parameterized view (which doesn't exist in standard T-SQL)
• Adding a new user means adding a new view
• Changes to the underlying table require updating every view
• There's no central policy object to audit or govern

The CREATE SECURITY POLICY approach is demonstrably better: one policy object, one function, one mapping table. Auditors can see exactly what's in place. DBAs can modify it in one place.

The Dynamic Data Masking Confusion

Dynamic Data Masking (DDM) is often confused with RLS. They solve different problems:

• DDM hides specific column values (e.g., showing XXX-XX-1234 instead of a full SSN) but returns the row
• RLS hides entire rows based on the caller's identity

You can use both simultaneously. A user might be able to see the row (RLS says yes) but see a masked version of sensitive columns (DDM says mask). Fabric Warehouse supports DDM. But implementing both is a configuration management challenge — make sure your test cases cover both dimensions.

---

Hands-On Exercise

This exercise consolidates everything you've learned into a single end-to-end implementation. Budget about 45-60 minutes.

Scenario

You're building data access controls for a healthcare analytics platform. The clinical.PatientVisits table contains patient visit records segmented by hospital department. Department managers should only see their own department's records. Medical directors see all departments in their hospital. The Chief Medical Officer sees everything.

Step 1: Create the Environment

CREATE SCHEMA clinical;
CREATE SCHEMA rls_security;
GO

CREATE TABLE clinical.PatientVisits (
    VisitID         INT             NOT NULL,
    PatientID       INT             NOT NULL,
    Department      VARCHAR(100)    NOT NULL,
    Hospital        VARCHAR(100)    NOT NULL,
    VisitDate       DATE            NOT NULL,
    DiagnosisCode   VARCHAR(20)     NOT NULL,
    BilledAmount    DECIMAL(12,2)   NOT NULL
);
GO

INSERT INTO clinical.PatientVisits VALUES
    (1, 101, 'Cardiology',     'City General',   '2024-01-10', 'I21.3',  4500.00),
    (2, 102, 'Oncology',       'City General',   '2024-01-12', 'C34.10', 12000.00),
    (3, 103, 'Cardiology',     'City General',   '2024-01-15', 'I50.9',  8200.00),
    (4, 104, 'Emergency',      'City General',   '2024-01-18', 'S72.001',3200.00),
    (5, 105, 'Cardiology',     'Metro Hospital', '2024-01-20', 'I25.110',7800.00),
    (6, 106, 'Neurology',      'Metro Hospital', '2024-01-22', 'G35',    9500.00),
    (7, 107, 'Emergency',      'Metro Hospital', '2024-01-25', 'T14.90', 2100.00),
    (8, 108, 'Oncology',       'Metro Hospital', '2024-02-01', 'C50.912',15000.00);
GO

Step 2: Build the Mapping Table

Design and create a rls_security.UserDeptAccess table that supports three access levels: department, hospital, and global. Insert at least five test users with different access combinations.

Step 3: Write the Predicate Function

Write an inline TVF rls_security.fn_PatientVisitsPredicate that:

• Takes @Department VARCHAR(100) and @Hospital VARCHAR(100) as parameters
• Returns 1 if the current user has global access
• Returns 1 if the current user has hospital-level access for the row's hospital
• Returns 1 if the current user has department-level access for the row's department AND the row's hospital

Step 4: Create and Enable the Policy

Apply the predicate as a filter on clinical.PatientVisits. Verify it appears in sys.security_policies.

Step 5: Test All Three Access Levels

Write a test script using EXECUTE AS USER / REVERT blocks that validates:

• A department manager sees only their department (in the correct hospital)
• A medical director sees all departments in their hospital but not other hospitals
• The CMO sees all rows
• A department manager from City General cannot see Metro Hospital records even for the same department

Step 6: Disable and Re-enable

Practice the operational pattern:

-- Disable for maintenance (e.g., during bulk load)
ALTER SECURITY POLICY rls_security.PatientVisitsPolicy WITH (STATE = OFF);

-- Verify all rows visible during maintenance
SELECT Hospital, Department, COUNT(*) as RowCount 
FROM clinical.PatientVisits 
GROUP BY Hospital, Department;

-- Re-enable
ALTER SECURITY POLICY rls_security.PatientVisitsPolicy WITH (STATE = ON);

---

Common Mistakes and Troubleshooting

Mistake 1: Policy created but user sees zero rows instead of their rows.

Cause: The user's UPN in USER_NAME() doesn't exactly match what's in the mapping table. Common culprits: case sensitivity differences, spaces, or domain aliases (e.g., user logs in as User@Contoso.Com but the table has user@contoso.com).

Fix: Run SELECT USER_NAME() in the session first, then compare to what's in the mapping table. Add a LOWER() call in both the function and the insert to normalize.

-- Defensive version of the predicate
CREATE FUNCTION security.fn_OrdersRegionPredicate(@Region VARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS fn_SecurityResult
    FROM security.UserRegionMap AS urm
    WHERE urm.Region = @Region
      AND LOWER(urm.UserPrincipalName) = LOWER(USER_NAME());

Mistake 2: Policy is ON but doesn't seem to filter for service principals or pipelines.

Cause: Pipelines and Spark notebooks that connect to the warehouse may use a service principal identity, and that identity may have the db_owner or dbo role, which bypasses RLS in some database engines.

Fix: In Fabric Warehouse, RLS applies even to db_owner unless you explicitly create a bypass mechanism. Verify what USER_NAME() returns in the pipeline's connection context. Add the service principal's object ID or UPN to the mapping table if needed, or use the bypass role pattern described earlier.

Mistake 3: Dropped and recreated the underlying table and the policy is now broken.

Cause: DROP TABLE followed by CREATE TABLE destroys the security policy binding. The policy still exists but references a nonexistent object ID, and queries will fail.

Fix: Use ALTER TABLE to modify columns rather than dropping and recreating. If you must recreate, script the policy drop and recreation as part of your deployment. This is another reason to manage your security policies in version-controlled scripts — ideally through Fabric Git Integration and Deployment Pipelines: Version Control and Promotion Across Environments.

Mistake 4: RLS works in the warehouse but not through Power BI Direct Lake.

Cause: This is not a mistake — it's expected behavior. Power BI Direct Lake connects to the lakehouse, not the warehouse, using a system identity. The SQL endpoint's RLS policies don't govern this path.

Fix: Implement RLS in the Power BI semantic model using DAX roles. This is a separate layer of security and should be considered complementary, not redundant. The two layers together — SQL-level RLS for direct SQL access, DAX-level RLS for Power BI — provide defense in depth. See how the lakehouse connects to Power BI in Connecting a Power BI Semantic Model to a Fabric Lakehouse in Direct Lake Mode: Creating, Refreshing, and Optimizing Delta Tables for Reporting and plan your security model accordingly.

Mistake 5: The predicate function was created without WITH SCHEMABINDING.

Cause: Without SCHEMABINDING, someone can alter or drop the underlying mapping table without removing the function first. The function becomes a reference to a nonexistent object, and queries against the secured table will fail with cryptic errors.

Fix: Always use WITH SCHEMABINDING. If you need to modify the mapping table schema, you'll need to drop the function first, alter the table, then recreate the function. This is a feature, not a bug — it forces deliberate action before breaking a security control.

Mistake 6: Testing only shows the right result for one user but the policy is incorrect for another.

Cause: Testing is incomplete. Teams often test the "happy path" (user sees their rows) but forget to test:

• A user with no mapping table entry at all (should see zero rows)
• A user who's been removed from the mapping table mid-session (the session might cache the results depending on the client)
• A user in two regions (should see both regions' rows)

Fix: Build a comprehensive test matrix before going to production. Test every access level combination, including edge cases like "user in zero regions" and "user in all regions."

---

Summary and Next Steps

You've built a complete, production-grade row-level security implementation from scratch. Let's recap the architecture you now own:

1. A dynamic mapping table that decouples user identities from the predicate logic — adding or removing a user's access is a single-row DML operation, not a code change
2. An inline TVF predicate with WITH SCHEMABINDING that the query optimizer can reason about efficiently
3. A security policy that applies transparently to every query path — direct SQL, views, stored procedures, external tools
4. An admin bypass mechanism using a dedicated database role, so infrastructure accounts can operate without being subject to data filtering
5. A systematic testing methodology using EXECUTE AS USER for in-session validation and real user testing for production verification

You also now understand the critical distinctions:

• RLS at the SQL endpoint level does not protect data accessed through Spark, the Files API, or Power BI Direct Lake
• Workspace roles are an access layer, not a data filtering layer — they complement but don't substitute for RLS
• The SQL Analytics Endpoint supports RLS syntax but has meaningful limitations around schema management and multi-path access

Where to Go From Here

If you're working on a medallion architecture and wondering where RLS fits into the bronze/silver/gold layers, you'll find the design decisions in Implementing the Medallion Architecture in Microsoft Fabric: Bronze, Silver, and Gold Layers — specifically, the gold layer is where RLS typically lives, on curated tables that business users query directly.

For teams managing multiple environments (dev, test, prod), your security policies need to be version controlled and deployed consistently. Your RLS scripts should be committed to Git and promoted through deployment pipelines alongside the table definitions they govern — the same pipeline that promotes your warehouse schema should also promote your security policies.

And if your organization is building Direct Lake Power BI reports on secured data, understand that you're working with two independent security stacks: T-SQL RLS for SQL-path access, and Power BI DAX roles for the report layer. Both need to be configured, tested, and maintained. A gap in either one is a data governance exposure.

RLS is one of those capabilities that looks straightforward in a 10-line example and reveals its depth when you're managing fifty users across eight tables in three environments. The architecture you've built here — separation of mapping data from predicate logic, schema-bound functions, systematic testing with impersonation — scales to that complexity. Start simple, stay systematic, and test from the user's perspective before you ship.