Forum Discussion
Granular access to specific objects within the Warehouse does not work using Power Desktop
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
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 :
Post that only specific objects are visible:
8 Replies
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 :
Post that only specific objects are visible: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 arroundsIf 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.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?
Hi,
It could be a caching issue. I had similar observation and I cleared permissions and re-authenticated. Then only Granted views are visible.
https://www.reddit.com/r/MicrosoftFabric/comments/1lk7vwd/comment/mzsigj6/
Regards,Nikhil Joy
