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

[cheryl0316]

I’m a Fabric workspace admin and I have a table containing confidential data in a Warehouse. I want ppl to be able to view a Power BI report built on top of this table, but not have access to the original table in the warehouse. Even other workspace admins should not be able to see the raw table unless explicitly granted permission.

The Power BI report uses a Direct Lake connection.

I’ve tried the following approaches so far:

1. Data masking
   This doesn’t work because users with Admin, Member, or Contributor roles in the workspace, and with elevated permissions on the warehouse—can still see the unmasked data.

2. Object-level security
   DENY SELECT ON blocks users from accessing the Power BI report as well

Any recommendation? Thanks in advance

[tayloramy]

Hi @cheryl0316, 

If you're granting contributor or higher rights to the workspace, there is pretty much nothing you can do to restrict the data. 

I recommend moving the data warehouse to a different workspace that has tighter access controls, and allowing report developers to connect to it from their report workspace. 

 THen for the report developers, don't grant them any workspace roles on the warehouse workspace, share the warehouse with them and use SQL Granular permissions to give them access to only the tables they need. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[KevinChant]

If the data is that confidential then maybe consider an alternative approach. Such as ingesting the data elsewhere and then making it anonymous in the table?

[Anusha66]

This if for power bi developers-  how about you create a semantic model on top of the table, create and use a gateway( not single sign-on) in the Gateway Connections. 

By doing this, user will not require access to the Warehouse table WS as gateway will be used for connection. so you dont have to give them any read persmission on WS containing the table.

[burakkaragoz · Accepted solution]

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.