Row-level security: Power BI, Fabric and SQL

Row-level security (RLS) makes the same table return different rows to different people, based on who is asking. The decision rule for exams: rows are hidden by RLS, columns and tables by object-level or column-level security, and the product decides the mechanism. In a Power BI semantic model RLS is a role with a DAX filter, in a Fabric warehouse or SQL database it is a security policy with a predicate function. Use static rules for a handful of fixed groups and dynamic rules, driven by the signed-in user, when each person sees something different.

Pick the layer first: which product holds the rule

Most RLS questions are really “where is the rule defined” questions.

Microsoft states that in a semantic model RLS does not apply to workspace Admin, Member or Contributor, because they can edit the model. In the SQL engine it is the opposite: a security policy applies to all users, including dbo, and if administrators must see all rows the predicate has to allow it.

Databricks has a third mechanism: Unity Catalog row filters and column masks, functions attached to a table or column, optionally applied at scale through tag-based ABAC policies. DP-750 and the Databricks data engineer exams test that version; Unity Catalog explained covers it.

Static versus dynamic RLS in a semantic model

A static rule hard-codes a value in the DAX filter, for example [Region] = "West". You need one role per value, and every new region means a new role. A dynamic rule compares a column to the identity of the viewer, for example [UserEmail] = USERPRINCIPALNAME(). One role serves everybody, and access is maintained as rows in a mapping table. The Microsoft documentation calls dynamic RLS the most common approach.

The details exam writers use:

• A DAX filter returns true or false per row. Rows that return true are visible, everything else is removed.
• Role members are assigned in the Power BI service, never in Desktop. Groups must be Microsoft Entra security groups, distribution lists or mail-enabled groups. Microsoft 365 groups are not supported.
• A user in several roles gets the union of what the roles allow. Roles are additive.
• In the service, USERNAME() and USERPRINCIPALNAME() both return the UPN. In Desktop USERNAME() returns DOMAIN\user.
• Service principals cannot be added to a role, so RLS is not applied when an app embeds with a service principal as the final identity. Embedded scenarios pass an effective identity instead.
• With Import mode, security roles in the source are not used. With DirectQuery, the source’s own security can apply too.
• RLS filters rows only. A user who sees a row sees all of its columns.

Worked example: one role, one mapping table

A sales model has a Sales fact table and a Region dimension. Add a UserRegion table:

Relate UserRegion[Region] to Region[Region] (many-to-one), then create one role with the filter [UserEmail] = USERPRINCIPALNAME() on UserRegion. By default a filter travels only from the one side (Region) to the many side, so set that relationship’s cross filter direction to Both and select Apply security filter in both directions. The filter then flows from UserRegion to Region and on to Sales, so Ana sees West and East rows and Raj sees South. Adding a region for Raj is a data change, not a model change. Test with View as or Test as role, and remember that it uses your own identity for dynamic rules, so guest accounts must be checked by signing in as the guest.

The same idea in T-SQL for a warehouse, where only filter predicates are supported in Microsoft Fabric and Azure Synapse:

CREATE FUNCTION Security.fn_region(@Region AS varchar(20))
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS ok
   FROM dbo.UserRegion u
   WHERE u.Region = @Region AND u.UserEmail = USER_NAME();

CREATE SECURITY POLICY RegionFilter
ADD FILTER PREDICATE Security.fn_region(Region) ON dbo.Sales
WITH (STATE = ON);

Object-level security hides what RLS cannot

Object-level security (OLS) in a semantic model hides whole tables or columns, and their metadata, from a role: for those viewers it is as if the object does not exist, and visuals that use it show an error. It is defined inside the same roles, with TMDL view or Tabular Editor, by setting metadataPermission to none. Like RLS it applies only to Viewers. In T-SQL the counterparts are GRANT/DENY on columns and dynamic data masking, covered with RLS in the DP-800 security domain.

SQL predicates: filter versus block

A filter predicate silently removes rows from SELECT, UPDATE and DELETE. A block predicate makes a write fail (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE). Filtering alone lets a user insert a row they then cannot see. SCHEMABINDING = ON is the default and avoids extra permission checks on the tables a predicate touches. Middle-tier apps that share one database login pass the end user with SESSION_CONTEXT. Microsoft also warns that crafted queries can leak information through errors, so RLS is not a substitute for limiting who can run arbitrary queries.

Which exams test this

How questions are built around RLS

The first trap is the workspace role. A scenario where RLS “does not work” for a user nearly always has that user as Contributor or higher; the fix is the Viewer role. The second is granularity: “hide the Salary column” is OLS or column-level security, not RLS. The third is the member list: roles defined in Desktop do nothing until users are added in the service. Constraint words: “each user sees their own” means dynamic, “fixed regions” can be static, “block inserts” means a block predicate, “no code” or “single role for 5,000 users” points to a mapping table. Related reading: storage modes change where the rule is enforced, and star schema modelling decides how the filter propagates.

Go deeper

• Row-level security with Power BI (Microsoft Learn)
• Object-level security with Power BI (Microsoft Learn)
• Row-level security in SQL Server and Azure SQL (Microsoft Learn)
• Row-level security in Fabric Data Warehouse (Microsoft Learn)

On this site: Zero Trust explained for least privilege in general, Unity Catalog explained for the Databricks equivalent, and how to study for certification exams.