Forum Discussion

alfBI's avatar
alfBI
Responsive Resident
1 year ago
Solved

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/fabr...
Warehouse
  • NandanHegde's avatar
    NandanHegde
    1 year ago

    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 [<<[email protected]>>];
     
    Post that only specific objects are visible:

     

v-lgarikapat's avatar
v-lgarikapat
Community Support
1 year ago

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's avatar
    alfBI
    Responsive Resident
    1 year ago

    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's avatar
      v-lgarikapat
      Community Support
      1 year ago

      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

      • v-lgarikapat's avatar
        v-lgarikapat
        Community Support
        1 year ago

        Hi alfBI ,

         

        I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept as Solution"

         

        Best regards,
        LakshmiNarayana        .