Direct Lake on SQL Endpoint + OneLake Security: A Field Guide to Delegated vs. User Identity Mode (Part 2)

How a single "You don't have permission to view the content of Direct Lake table" error sent me down a rabbit hole — and everything I learned about securing cross-lakehouse shortcuts, native tables, and SPN-owned semantic models in Microsoft Fabric.

Contd.. from part 1 - https://community.fabric.microsoft.com/t5/Fabric-platform-Community-Blog/Direct-Lake-on-SQL-Endpoint-OneLake-Security-Delegated-vs-User/ba-p/5358560

User identity mode

Governing rule: the SQL EP passes the signed-in user's identity to OneLake; table reads are governed by OneLake security roles. SQL GRANT/REVOKE on tables is not allowed. Non-table objects (views, procs, functions) still use SQL permissions.

The one-to-one identity mapping (the classic gotcha)

Historically, User identity mode required a strict one-to-one mapping:

1. Fabric Read on the Consumer — the exact principal in the OneLake role must be directly granted Read on the Consumer lakehouse (role membership alone is not enough).
2. Object ID must match at Producer ↔︎ Consumer — nested/effective group membership isn't resolved across that boundary.
3. Artifact Read on the SQL EP — always required.

🟢 The redesign relaxes this — the newest update reduces the cross-lakehouse one-to-one friction and improves nested-group + SPN support (including SPN-owned lakehouses). But Microsoft Learn still documents the strict behavior, so treat the relaxation as rolling out and verify on your tenant.

"Will a user in BOTH groups work?"

Yes. A viewer in both SD-Fabric-ConsumerAnalyst-Viewer (Build) and SD-DataAccess_Master_DataSA-RO (OneLake role + Consumer Read) renders the visual — guaranteed regardless of rollout state.

Permission matrix (user identity)

Level Principal Grant / Why

[[ IMAGE: user-identity-mode.png — User identity mode: the signed-in viewer's identity is passed to OneLake, and data access is authorized by the OneLake role via SD-DataAccess_Master_DataSA-RO. The viewer must be in both groups. ]]

Mixed sources: shortcut and native tables

This is where the two modes diverge most. Suppose the Consumer Lakehouse holds:

• (A) a shortcut table → daily_taxi_summary (data physically in the Producer, gold.daily_taxi_summary)
• (B) a native Delta table → silver.trip_events (data physically in the Consumer)

Key principle #3 — where the data lives = where security is defined

Table Physical data Governed on Object-ID mapping?

What this means per mode

Delegated mode — one surface. Both tables are queried through the same SQL EP, so a single GRANT SELECT on the Consumer EP authorizes both. Only the shortcut needs the owner's ReadAll on the Producer.

-- Delegated: one surface covers both
GRANT SELECT ON [gold].[daily_taxi_summary] TO [SD-DataAccess_Master_DataSA-RO];
GRANT SELECT ON [silver].[trip_events]      TO [SD-DataAccess_Master_DataSA-RO];

[[ IMAGE: delegated-mixed-sources.png — Delegated mode with mixed sources: one SQL GRANT surface on the Consumer EP authorizes both the shortcut (A) and native (B) tables. Only the shortcut fetch reaches the Producer via the owner identity. ]]

User identity mode — two surfaces. The shortcut is governed by a OneLake role on the Producer; native tables by a OneLake role on the Consumer. A viewer must be authorized on both, or you get partial failure:

⚠️ Partial failure: a viewer in the Consumer role only will see native visuals render while the shortcut visual throws QueryUserError — on the same report page.

⚠️ DefaultReader caveat: enabling OneLake security on the Consumer governs its native tables too. DefaultReader preserves prior access; tightening it can silently lock out native tables unless you add the group to the Consumer role.

[[ IMAGE: user-identity-mixed-sources.png — User identity mode with mixed sources: the shortcut (A) is authorized by Role #1 on the Producer; native tables (B) by Role #2 on the Consumer. One model reads both surfaces — miss one and that table's visuals fail while others render. ]]

Refresh behavior (don't get caught)

A Direct Lake refresh is a reframe that reads Delta metadata. At refresh time there's no interactive user — the SPN owner is the reader.

• Delegated: SPN needs Item Read + ReadData on the Consumer EP; the owner identity still fetches the shortcut from the Producer.
• User identity: the SPN must be in the OneLake role(s) (Producer for shortcut, Consumer for native) with direct Consumer Read — or the corresponding table fails on scheduled refresh even though user queries work.

🪤 Common trap: reports render for users (identity passthrough) but scheduled refresh fails because the SPN owner was never added to the OneLake role.

Delegated vs. User identity — side by side

Dimension Delegated identity User identity (new default)

The operational risk you'll actually hit: group reconciliation

Because data access (SD-DataAccess_Master_DataSA-RO) and app access (SD-Fabric-ConsumerAnalyst-Viewer) ride on two separate groups, their memberships can silently drift:

• Added to the app group but not the data group → report opens, every visual = QueryUserError.
• In the data group but not the app group → can't open the report at all.

It's the primary risk because it's frequent (membership changes constantly), silent (nothing errors at config time), and user-facing (it produces the exact error end users see). Fixes: collapse to one group, nest one inside the other, or automate the sync (dynamic groups).

Validation checklist

1. Confirm the Consumer SQL EP access mode (Delegated vs. User identity).
2. Delegated: GRANT SELECT to the data group on both tables; verify owner ReadAll on the Producer (shortcut).
3. User identity: group in the Producer role (shortcut) and Consumer role (native); direct Consumer Read + Object-ID match.
4. Add the SPN to the required role(s) so scheduled refresh succeeds.
5. Test each source with its own visual to catch partial failures.
6. Verify DefaultReader handling on the Consumer before tightening.
7. Negative test: a user in only the app group should fail — confirms group separation.

Takeaways

• Model ownership ≠ data access. With SSO, the viewer's identity is the reader.
• Know your mode. Delegated = SQL-governed & uniform; User identity = OneLake-governed & centralized.
• Mixed sources multiply surfaces. One SQL grant (delegated) vs. two OneLake roles (user identity).
• Refresh uses the SPN. Authorize the SPN explicitly, or scheduled refresh breaks silently.
• Reconcile your groups — or better, collapse them.
• Verify the redesign on your tenant before relying on relaxed nesting/Object-ID behavior.

GitHub Link: https://github.com/ajit503/fabric-field-notes/blob/main/direct-lake-on-sqlep-onelake-security/direct-lake-on-sqlep-onelake-security.md

Written by Ajit Kumar Singh — Fabric Admin & Architect. Based on hands-on troubleshooting of Direct Lake on SQL Endpoint with OneLake security across producer/consumer lakehouses.