How to Configure Row-Level Security (RLS) in a Microsoft Fabric Data Warehouse

Most Power BI practitioners and users I talk to are pretty comfortable with row-level security. Simply put, you build a security/mapping table, hook it up to your dim with right relationship, write a DAX role that filters on USERPRINCIPALNAME() for dynamic RLS, and you are off. When a user signs in, it propagates from mapping table, to dimension table and finally fact table, only displaying relevant rows for logged-in user.

However, since the arrival of Microsoft Fabric, RLS is no longer something we only think about in the Power BI semantic model. We have RLS in the Fabric Warehouse and also have OneLake Security RLS, which is currently in preview and can be configured from the Lakehouse experience for tabular data stored in OneLake.

On the surface, they all do a similar job. It is Row-Level Security after all... limiting which rows a logged-in user can see. But they are configured in different places, enforced at different layers and they do not behave the same way when you scratch beneath the surface.

RLS outside Power BI is not new, but Fabric brings those previous back-end workloads much closer to Power BI practitioners. So, the question now is not only what is RLS purely from a Power BI perspective, but how do you actually configure it in the Data Warehouse?

That is what this blog will cover, end to end, with a real working demo. So, it will focus on the Microsoft Fabric Data Warehouse. I will leave the comparison between Warehouse RLS and Power BI semantic model RLS and where each one belongs in a wider architecture, for a follow-up blog.

The Three Pieces You Need for Row-Level Security (RLS)

Configuring Warehouse RLS comes down to a mapping table plus three steps. Once you see them in this order, it stops feeling mysterious. First, we have the supporting table:

A mapping table: who can see what.

Below are the three steps:

1. An inline table-valued function: the rule that compares the logged-in user against the mapping table
2. A security policy: attaches the rule (function) to a specific table (fact table)
3. Turn it on: STATE = ON activates enforcement

If you follow our blogs, you know I love a good diagram. Great way to receive needed insights at a glance. So, the full pattern looks like this when you draw it out:

‍

‍

For the rest of this blog, let me walk through each piece with the actual SQL. I am working in a Warehouse called WH_RLS_Demo with a small star schema (all dummy data I asked AI to produce). It contains one fact (sales.fact_sales), four rows in the (sales.dim_region) and a user-region mapping table called (security.sec_user_region). I have more tables, but these are the only relevant ones. Sharing below, as i think will help you make more sense of the SQL further below.

‍

‍

The Mapping Table

The mapping table is the simplest part technically, but it is also the most important, and arguably the hardest part of dynamic RLS to get right in the long run. Not because of the SQL, but because of who owns it.

‍

CREATE TABLE security.sec_user_region (
    user_email   VARCHAR(100) NOT NULL,
    region_id    INT          NOT NULL
);

‍

In our example here we are using a user to region mapping because the demo is built around regional sales, but in practice the columns reflect whatever business rule you need to enforce. It might be user to customer, user to cost centre, user to market segment. The shape changes, the principle does not.

One row per (user, region) combination. A user with access to two regions has two rows. A global admin who needs to see all four regions has four. A user who should see nothing has zero. Permissions are data, not code and that is what makes this pattern much more flexible than hardcoding identities into a function.

But here is the hard part, and the bit I see clients underestimate every time we set this up on the PBI side. Once the table is in production, who maintains it? If a regional manager is promoted to cover a second region, who adds the row? When someone leaves the business, who removes them? If the answer is "the BI team" that may not be the best move... the BI team are not the source of truth on who works where. I say the strongest implementations embed maintenance into an existing process. For example, if HR already maintain a structure of who belongs to which region or IT if they handle joiners and movers and leavers and its reflected in Entra ID (previously AAD). Pulled from there into the mapping table on a schedule, ideally automated. If the mapping table goes stale, the RLS logic still runs, it just enforces the wrong rules.

The function and the policy you write once. The mapping table you must maintain forever and ensure it is accurate, not only for MS Fabric Warehouse but also for PBI Semantic Models when working purely on the PBI side.

For this demo, here is how my mapping table is set up:

‍

‍

Step 1: Create the security rule (the inline TVF)

This is where the access rule lives. The security policy will later use this inline table-valued function to decide which rows a user is allowed to see.

‍

CREATE FUNCTION security.fn_region_access_predicate (@region_id AS INT)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN
    SELECT 1 AS access_granted
    FROM security.sec_user_region AS sur
    WHERE sur.region_id  = @region_id
      AND sur.user_email = USER_NAME();

‍

A few things are worth pulling out about this function.

It is an inline table-valued function. That sounds more complicated than it is so simply put it means the function returns a table shaped result from one query. It is not a multi-statement TVF, where the function does several steps before returning a table and it is not a scalar function, where the function returns one value such as a number, date or text. Microsoft documents Fabric Warehouse RLS as using inline TVFs as the security predicate behind the policy. Because the function is inline, the database engine can see the filtering logic inside it and optimise it as part of the main query. It still has to apply the security check, so it is not free, but it avoids the classic row-by-row function problem that can hurt performance on larger tables.

WITH SCHEMABINDING is also important. It binds the function to the objects it depends on, so you cannot casually drop or alter the mapping table: sec_user_region or rename the columns the function relies on while the security setup depends on them. Annoying when you want to make a quick change, but useful when this logic is protecting data access. So, if someone three months from now decides to “tidy up” the security or mapping tables, they get an error rather than silently breaking your security model.

Also notice that the function uses USER_NAME() to identify the current user. In Fabric, you should always test this with SELECT USER_NAME() as the relevant user and make sure the value stored in your mapping table matches what Fabric actually returns for that user. The function also takes @region_id as a parameter. That is what the policy will pass in for every row of the protected table.

The easiest way to think about it is this. Every row in the protected table, which in our case is sales.fact_sales, has a region_id. For each row, Fabric passes that region_id into the function and checks the mapping table. It is basically asking: "Is this user allowed to see this region?". If the answer is yes, the function returns a row and the data row stays visible. If the answer is no, the function returns nothing and the data row disappears from the result. That is why the 1 is irrelevant because Fabric is not interested in the value itself only whether the function returns a row at all.

Step 2: Attach the rule (the security policy)

For the function discussed above, alone it does nothing. It is just a thing we can call, that sits in the database. If we are to query fact_sales right now, the engine has no reason to involve it. The wiring happens in the security policy:

‍

CREATE SECURITY POLICY security.region_access_policy
ADD FILTER PREDICATE security.fn_region_access_predicate(region_id)
    ON sales.fact_sales
WITH (STATE = ON);

‍

Two things this statement does at once:

• Specifies which table to protect: ON sales.fact_sales
• Specifies which column to feed into the predicate: (region_id)

The third clause, WITH (STATE = ON), activates the policy. However, we will come back to it in Step 3 further below as it deserves its own section.

The mechanics from this point are fairly simple. Every query against sales.fact_sales is automatically filtered by the engine using the predicate, so the security rule from the function. You do not need to change your SQL and tools like Power BI, SSMS, notebooks or other applications do not know that extra filtering is happening. They just receive the result set they are allowed to see. If rows are filtered out, those tools are not told “some rows were hidden”, they simply see fewer rows.

Step 3: Turn it on (and the trick for debugging)

In Step 2, we ran WITH (STATE = ON) at the end of the CREATE SECURITY POLICY statement. That single clause is what activates the policy. From the moment it ran, every query that reads from sales.fact_sales started being filtered through the predicate - the security rule, which is the function from Step 1. If the policy had been created with STATE = OFF instead, the function and the policy would still exist, but no filtering would happen.

That on/off switch is more useful in practice than it looks. The state can be flipped at any time with a single statement:

‍

-- Turn enforcement off
ALTER SECURITY POLICY security.region_access_policy
WITH (STATE = OFF);
GO

-- Do whatever required RLS to be temporarily off...

-- Turn it back on
ALTER SECURITY POLICY security.region_access_policy
WITH (STATE = ON);
GO

‍

When STATE = OFF, the entire RLS setup stays intact. The function, mapping table and policy exist. The only thing that stops is enforcement of the predicate. Queries against the protected table return unfiltered data again, just as if RLS was not there. Flip the state back to ON and filtering resumes immediately. No reattach, no rebuild.

When a user reports something like "I should see this row but I do not"... STATE = OFF is one option for narrowing the issue down. With the policy on, you do not always know whether the issue is the predicate logic, a missing row in the mapping table, a typo in their user value or the wrong region_id. Toggling the policy off temporarily lets the user run the same query without RLS being enforced. If they see the row they expected, you have isolated the problem to the security setup rather than the underlying data. If they still do not see it, the issue is somewhere else. Just be careful with this in production, because while the policy is off, users can see unfiltered data.

I tested exactly this with the test user. With STATE = ON, they see £104,250 across North and South only. I ran ALTER SECURITY POLICY ... WITH (STATE = OFF) from my own session, the test user re-ran the same query, and they suddenly saw £216,450 across all four regions. Same user, same query, same warehouse. Only the policy state changed. Then I flipped it back to ON and the test user was filtered again.

‍

Wrapping up

Warehouse RLS comes down to one mapping table and three steps: create the inline TVF, attach it to a table with a security policy and switch enforcement on with STATE = ON. The function defines the rule. The policy attaches the rule to a table. STATE = ON enforces it. Once you have seen two users run the same query and get different results, the rest starts to feel a lot less mysterious.

Warehouse RLS is one flavour of three in Fabric. How it sits alongside OneLake Security RLS and Power BI semantic model RLS and which one belongs where in a wider architecture... is a topic for a follow-up blog.

If your team is wrestling with row-level security across Fabric or Power BI, or you have run into a sharp edge with Warehouse RLS that I have not mentioned here, get in touch. At Metis BI we work with clients to design and implement the kind of layered, maintainable security models this blog is built on.

‍