View Power BI report while restricting access to underlying Warehouse table in direct lake mode

Hi cheryl0316 ,

This is a very common security architectural challenge in Fabric. The core conflict you are facing is that Direct Lake acts like a "Pass-Through" mechanism: for the report to show data to the user, the user’s identity must have SELECT permissions on the underlying data. This is why DENY SELECT breaks your report immediately.

Furthermore, the Workspace Admin role is effectively a "Super User" for that specific workspace. You technically cannot hide data from an Admin within the workspace they own, as they have inherent rights to override permissions or query endpoints directly.

To achieve your goal (Report Viewers = YES, Raw Table Access = NO), you need to change your workspace architecture and sharing strategy. Here is the recommended approach:

1. Solve the "Admin" Problem (Workspace Separation)

You cannot restrict other Workspace Admins/Members from querying the Warehouse if the data lives in the same workspace as them.

• The Fix: Create a separate "Secure Data" Workspace.

• Action: Move (or recreate) your Warehouse in this secure workspace.

• Permissions: You (and perhaps one backup) are the only Admins here. Remove all other users. This isolates the "Raw Table" access.

2. Solve the "Viewer" Problem (The App Strategy)

Now that the data is secured, you need to give users access to the Report without giving them access to the Warehouse Editor (SQL Endpoint).

• Create the Semantic Model: In the Secure Workspace, create the Direct Lake Semantic Model.

• Create the Report: Build your report connected to that model.

• Publish an App: Publish the report as a Power BI App (or share the report directly).

• The Key Setting: When sharing/publishing the App, grant users "Read" permission on the dataset, but DO NOT grant "Build" permission if possible.

  • Note: Users need Read rights for the Direct Lake query to execute.

  • Result: Because they are not "Members" or "Contributors" of the Secure Workspace, they cannot navigate to the Warehouse item or open the SQL Endpoint to run SELECT * FROM SecretTable. They can only interact with the pre-built visuals in the Report.

3. Alternative: Fixed Identity (Trade-off Required)

If you absolutely must keep everyone in the same workspace (not recommended for this scenario) or require stricter locking, you would have to abandon Direct Lake.

• Switch to DirectQuery: You can switch the storage mode to DirectQuery and use a Fixed Identity (e.g., a Service Principal or a specific SQL User) to connect to the Warehouse.

• The Outcome: The Gateway/Connection has access to the table, but the User does not.

• The Cost: You lose the performance benefits of Direct Lake.

Recommendation Stick with Direct Lake, but move the Warehouse to a dedicated workspace where you are the sole Admin. Share the content via an App so users consume the report without ever having the interface permissions to open the Warehouse directly.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.