Granular access to specific objects within the Warehouse does not work using Power Desktop

[alfBI]

Hi,

We have a Warehouse where we want to restrict access to certain tables when users connect to its SQL End Point. We followed the steps described here

https://learn.microsoft.com/en-us/fabric/data-warehouse/share-warehouse-manage-permissions

first providing "Read" role to user on the Warehouse

and then assigning specific granular rights using T-SQL as follows

GRANT SELECT ON Star.COM.DimOBS TO [pbi-test@XXXX];
GO

Following query confirm provided rights to user

when connecting to the WH SQL Endpoint thinks works as expected

only the object with assigned rights is visible, but the problem appears when using the same account we use power BI Desktop and we connect to the Warehouse SQL Endpoint

Then surpreisingly all the tables of the WH are visible 😞

Why using SQL Endpoint with PBI Desktop OLS (Object Level Security) does not apply? It's a limitation or a bug?

Regards,

Alfons

[NandanHegde]

Please follow the below steps  :

My source Warehouse :

Share the warehoue with the user to grant him/her connect access by disabling all features:

Then login to Warehouse and execute the below queries :

GRANT SELECT ON <<TableNme>> TO [<<user@domain.com>>];

Post that only specific objects are visible:

[v-lgarikapat]

Hi @alfBI ,

Thanks for reaching out to the Microsoft fabric community forum.

OLS is enforced within the semantic model in Power BI meaning it works when users interact with reports or datasets published to the Power BI Service. However, when you connect to a Warehouse SQL Endpoint using Power BI Desktop , you're bypassing the semantic model and directly querying the underlying data source

Below are the few work arrounds

If you need to enforce table-level restrictions when users connect via SQL Endpoint

• Continue using T-SQL GRANT/DENY statements for access control.
• Consider using Row-Level Security (RLS) or OLS within the semantic model for Power BI reports.
• If security is a major concern, you might explore private endpoints and network-level restrictions to limit SQL access paths.

Solved: Re: Fabric SQL Analytics Endpoint to Power BI Desk... - Microsoft Fabric Community

Object-Level Security (OLS) with Power BI - Microsoft Fabric | Microsoft Learn

Develop Direct Lake semantic models - Microsoft Fabric | Microsoft Learn

Solved: Re: RLS/OLS role effect on Co Pilot Q&A - Microsoft Fabric Community

Solved: Power BI Service RLS/OLS to semantic model - Microsoft Fabric Community

If this post helped resolve your issue, please consider the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana.

[alfBI]

Ho v-lgarikapat,

Sorry, I use the word OLS erroneously.

Our aim is to prevent that a power BI desktop user might access certain tables of the warehouse when implementing a new semantic model using the warehouse sql end point.

We tried to do it exactly what do you mention (using T-SQL GRANT/DENY statements for access control), but it seems not to work (see above screenshots). 

Any idea about it does not work as expected?

[v-lgarikapat]

Hi @alfBI ,
Thank you for your follow-up question.

@NandanHegde,
Appreciate your prompt and detailed response.

@alfBI when you get a chance, could you please try the solution shared by @NandanHegde ? It outlines a clear step-by-step approach that may help resolve the issue.

If you continue to experience any difficulties, please don’t hesitate to reach out. We're happy to support you further.

Looking forward to your feedback.

Reference:

Lakehouse sharing and permission management - Microsoft Fabric | Microsoft Learn

Best regards,
LakshmiNarayana