MS-SQL Row-Level Security That Actually Works with Power BI and Fabric Semantic Models

What “Plays Nicely” Actually Means

When we say RLS that works well with Power BI / Fabric semantic models, we want:

• Single source of truth for permissions (no duplicated logic in SQL and in Power BI roles).
• Predictable performance under DirectQuery and import.
• Simple mental model for analysts building reports.
• Easy onboarding for new users (ideally driven by AD groups, not manual lists).
• Auditable and testable behavior.

You don’t need every possible RLS feature. You need a pattern you can repeat.

Core Pattern: Security Tables + Predicate Functions

Forget ad-hoc WHERE clauses. The pattern that scales is:

1. Security dimension tables describing who can see what.
2. Stable business keys linking data to those security dimensions.
3. Inline table-valued function (iTVF) that calculates allowed keys for the caller.
4. Security policy that uses the function as a filter predicate.

Example Scenario

Classic: sales data, restricted by region.

• Users belong to one or more regions.
• Some users (e.g., managers) see multiple regions.
• Power BI uses DirectQuery or import from the same database.

1. Security Tables

CREATE SCHEMA sec;
GO

CREATE TABLE sec.UserRegion
(
    UserPrincipalName  nvarchar(256) NOT NULL,
    RegionKey          int           NOT NULL,
    CONSTRAINT PK_UserRegion PRIMARY KEY (UserPrincipalName, RegionKey)
);

CREATE TABLE dbo.Region
(
    RegionKey int          NOT NULL CONSTRAINT PK_Region PRIMARY KEY,
    RegionCode nvarchar(50) NOT NULL,
    RegionName nvarchar(200) NOT NULL
);

CREATE TABLE dbo.Sales
(
    SalesId     int IDENTITY(1,1) PRIMARY KEY,
    OrderDate   date         NOT NULL,
    CustomerId  int          NOT NULL,
    RegionKey   int          NOT NULL,
    Amount      decimal(18,2) NOT NULL,
    CONSTRAINT FK_Sales_Region FOREIGN KEY (RegionKey) REFERENCES dbo.Region(RegionKey)
);

Key points:

• RegionKey is the bridge between RLS and the fact table.
• UserPrincipalName will match the UPN that Power BI passes through when using SSO.

2. Predicate Function

Use an inline TVF so the optimizer can do its job.

CREATE FUNCTION sec.fnUserRegionPredicate(@upn nvarchar(256))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT 1 AS Allowed
    FROM sec.UserRegion ur
    WHERE ur.UserPrincipalName = @upn
);
GO

3. Security Policy

CREATE SECURITY POLICY sec.SalesRegionRLS
ADD FILTER PREDICATE sec.fnSalesRegionFilter(UserPrincipalName())
ON dbo.Sales
WITH (STATE = ON);
GO

We need a wrapper function that ties UPN to the Sales table:

CREATE FUNCTION sec.fnSalesRegionFilter(@upn nvarchar(256))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT 1 AS Allowed
    FROM dbo.Sales s
    JOIN sec.UserRegion ur
        ON ur.RegionKey = s.RegionKey
       AND ur.UserPrincipalName = @upn
);
GO

This is the core RLS: every query against dbo.Sales is transparently filtered by the caller’s regions.

Aligning with Power BI: Where RLS Lives

You have three options:

1. RLS in SQL only (no RLS in Power BI).
2. RLS in Power BI only (no SQL RLS).
3. Hybrid (some rules in SQL, some in Power BI).

For enterprise setups with Fabric or central semantic models, option 1 is usually the cleanest:

• One place to debug.
• Same behavior for all consuming tools.
• Works for both DirectQuery and import.

Use Power BI roles only when:

• You’re prototyping and don’t control the database.
• You have cross-source logic that SQL can’t see.

How Power BI Authenticates to SQL

For RLS to apply per user, Power BI must connect using:

• Azure AD SSO (for Azure SQL / Fabric / Synapse), or
• Kerberos delegation (for on-prem SQL via gateway).

If the model uses a single technical account, SQL sees that account for every query and RLS becomes useless.

Quick checks:

• In Azure SQL, run:

SELECT ORIGINAL_LOGIN() AS OriginalLogin,
       SUSER_SNAME()   AS CurrentLogin,
       SESSION_CONTEXT(N'AzureADTokenUPN') AS UPN;

• Make sure UPN matches the email address you store in sec.UserRegion.

Designing the Security Model for Fabric Semantic Models

Fabric semantic models are just Power BI datasets with a new name, but the same rules apply.

Key design decisions:

1. Use Natural Business Axes for Security

Common dimensions to secure by:

• Region / country / market
• Legal entity / company code
• Business unit / department
• Customer / partner account

Pattern:

• Put the security key in the dimension, not only in the fact.
• Drive RLS off that dimension, then let relationships propagate.

Example: secure by CompanyKey on DimCompany, not directly on every fact.

CREATE TABLE dbo.DimCompany
(
    CompanyKey   int           NOT NULL PRIMARY KEY,
    CompanyCode  nvarchar(50)  NOT NULL,
    CompanyName  nvarchar(200) NOT NULL
);

CREATE TABLE sec.UserCompany
(
    UserPrincipalName nvarchar(256) NOT NULL,
    CompanyKey        int           NOT NULL,
    CONSTRAINT PK_UserCompany PRIMARY KEY (UserPrincipalName, CompanyKey)
);

RLS function:

CREATE FUNCTION sec.fnCompanyFilter(@upn nvarchar(256))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT 1 AS Allowed
    FROM dbo.DimCompany c
    JOIN sec.UserCompany uc
        ON uc.CompanyKey = c.CompanyKey
       AND uc.UserPrincipalName = @upn
);
GO

CREATE SECURITY POLICY sec.CompanyRLS
ADD FILTER PREDICATE sec.fnCompanyFilter(UserPrincipalName())
ON dbo.DimCompany
WITH (STATE = ON);
GO

In the semantic model, relate DimCompany to all relevant fact tables. RLS on DimCompany will naturally filter facts.

2. Avoid Row-Level Security on Wide Fact Tables When Possible

Applying RLS directly on a massive fact table can hurt performance, especially in DirectQuery.

Prefer:

• RLS on narrow dimensions (like DimCompany, DimRegion).
• Let Power BI relationships do the heavy lifting.

Only put RLS on facts when:

• There is no clean dimension for the security key.
• Security is defined on attributes unique to the fact (e.g., specific transaction IDs).

Mapping Users and Groups: Don’t Hardcode Individuals

Hardcoding individual users into sec.UserRegion doesn’t scale.

Better patterns:

1. AD group-based security

   • Map AD groups to regions/companies.
   • Map users to AD groups outside SQL.

2. Role-based security

   • Define roles like Sales_Manager, Sales_Rep.
   • Map roles to regions, and users to roles.

Example with roles:

CREATE TABLE sec.Role
(
    RoleId   int IDENTITY(1,1) PRIMARY KEY,
    RoleName nvarchar(100) NOT NULL UNIQUE
);

CREATE TABLE sec.UserRole
(
    UserPrincipalName nvarchar(256) NOT NULL,
    RoleId            int           NOT NULL,
    CONSTRAINT PK_UserRole PRIMARY KEY (UserPrincipalName, RoleId),
    CONSTRAINT FK_UserRole_Role FOREIGN KEY (RoleId) REFERENCES sec.Role(RoleId)
);

CREATE TABLE sec.RoleRegion
(
    RoleId    int NOT NULL,
    RegionKey int NOT NULL,
    CONSTRAINT PK_RoleRegion PRIMARY KEY (RoleId, RegionKey),
    CONSTRAINT FK_RoleRegion_Role FOREIGN KEY (RoleId) REFERENCES sec.Role(RoleId)
);

Predicate function now joins through roles:

CREATE FUNCTION sec.fnSalesRegionFilter(@upn nvarchar(256))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT DISTINCT 1 AS Allowed
    FROM dbo.Sales s
    JOIN sec.RoleRegion rr
        ON rr.RegionKey = s.RegionKey
    JOIN sec.UserRole ur
        ON ur.RoleId = rr.RoleId
       AND ur.UserPrincipalName = @upn
);
GO

Now changing access becomes:

• Add/remove users from roles.
• Add/remove regions from roles.

No change to RLS logic itself.

Testing RLS for Power BI and Fabric

Don’t trust RLS until you’ve tested it from both sides: SQL and Power BI.

1. Test in SQL

Use EXECUTE AS USER to simulate a login.

EXECUTE AS USER = 'user1@contoso.com';
SELECT TOP 10 * FROM dbo.Sales;
REVERT;

Check:

• Rows are filtered as expected.
• Aggregates (SUM, COUNT) match what the business expects.

2. Test in Power BI

• Use View as in Power BI Desktop (when using Power BI roles).
• For SQL RLS with SSO:
  • Publish to the service.
  • Assign test users.
  • Ask them to open the report and verify counts.

If you must debug in DirectQuery:

• Turn on Performance Analyzer in Power BI Desktop.
• Copy the SQL query and run it in SSMS as the same user (via EXECUTE AS).

Performance Considerations

RLS can be cheap or expensive depending on design.

Keep it cheap by:

• Using inline TVFs, not multi-statement table-valued functions.
• Keeping security tables small and indexed.
• Filtering via keys, not text attributes.
• Avoiding complex logic inside the predicate (no heavy scalar functions).

Example indexes:

CREATE INDEX IX_UserRegion_User ON sec.UserRegion(UserPrincipalName, RegionKey);
CREATE INDEX IX_UserCompany_User ON sec.UserCompany(UserPrincipalName, CompanyKey);

For DirectQuery:

• Watch for predicates that prevent partition elimination.
• Ensure that your security key is part of clustered or partitioning keys when possible.

How This Interacts with Power BI Row-Level Security

If you also define RLS roles in Power BI:

• SQL RLS runs first, limiting rows returned.
• Power BI RLS then filters what’s already been reduced.

This can be useful for:

• SQL RLS: legal / compliance boundaries.
• Power BI RLS: presentation-level restrictions (e.g., hiding future periods).

But beware:

• Debugging becomes harder (two layers of filters).
• Performance tuning requires checking both sides.

Whenever possible, keep data access rules in SQL and UX-level rules in DAX (e.g., hiding measures), not RLS.

One Practical Takeaway

Before you build your next semantic model, decide one canonical security axis (region, company, or customer), model it as a dimension with a clean key, and implement SQL RLS only on that dimension using an inline TVF and a security policy. Then point Power BI or Fabric at that model with SSO enabled. You’ll get consistent, auditable row-level security that behaves the same way in every report, without fighting conflicting rules in multiple layers.