topic Re: How to Grant Access to an Individual Table/View in SQL Analytics Endpoint for a Lakehouse? in Data Engineering

How to Grant Access to an Individual Table/View in SQL Analytics Endpoint for a Lakehouse?

mramesh — Wed, 13 Nov 2024 09:33:40 GMT

Hi Community,
I'm working with the Lakehouse in Microsoft Fabric, and I need to provide access to a specific table or view through the SQL Analytics endpoint. Can anyone share the steps or best practices for granting access to an individual table/view via SQL Analytics, without giving broader access to the entire Lakehouse or other tables/views?

Re: How to Grant Access to an Individual Table/View in SQL Analytics Endpoint for a Lakehouse?

VenuBollineni — Wed, 13 Nov 2024 09:52:09 GMT

Hi @mramesh Good Day!

For example, to give a specific user access to a view, you can use T-SQL

GRANT SELECT ON [lakehouse_demo].[dbo].[Customer10] TO [username];

For doc pls check below
What is the SQL analytics endpoint for a lakehouse? - Microsoft Fabric | Microsoft Learn
Lakehouse sharing and permission management - Microsoft Fabric | Microsoft Learn

Regards,
Venu Bollineni

Re: How to Grant Access to an Individual Table/View in SQL Analytics Endpoint for a Lakehouse?

AndyDDC — Wed, 13 Nov 2024 09:56:35 GMT

Hi @mramesh further to what @VenuBollineni said, you also need to "share" the lakehouse with the user (or group). Make sure you don't check any of the options, doing this will only give the user "connect" permissions to the Lakehouse SQL Endpoint. Then the object level permissions as described by @VenuBollineni will kick in

Re: How to Grant Access to an Individual Table/View in SQL Analytics Endpoint for a Lakehouse?

fdnavarropecci — Wed, 11 Dec 2024 11:51:42 GMT

Hi, I have been testing this as well and found some unexpected behaviour.

If someone has experience with item-level access control and grants on specific views and sees a mistake in my process here, please chip in. Otherwise this looks like a bug/flaw to me.

Use Case and Setup Description

We have a workspace with three Lakehouse items: Bronze, Silver and Gold.

We want to expose certain views on the gold layer as a data product.

The access should be granted only to users that belong to a given AD Group.

The group is granted Read access to the Lakehouse Item. No ReadData or ReadAll permissions are given.

The group does not have any roles assigned at workspace level.

The group is in addition granted select on certain views in the Gold Lakehouse.

The expected behavior would be that the users in that group can list the tables and views in the Gold Lakehouse via the SQL Endpoint and read the data from the views they have been granted access to. We would expect that the users can't see the tables and views, nor read the data in the Bronze and Silver Lakehouses.

Findings

There is a single SQL endpoint for the whole workspace, not one per Lakehouse. We initially were under the impression that each Lakehouse had a separate SQL endpoint. If a Warehouse item is added to the same workspace, it will also share the same SQL endpoint.
Once granted read to one of the Lakehouses, users can see and read data in every other Lakehouse/Warehouse via the SQL endpoint, even if there is no access specifically granted to them to those items.

Re: How to Grant Access to an Individual Table/View in SQL Analytics Endpoint for a Lakehouse?

PatChan — Wed, 07 May 2025 06:26:01 GMT

Hi @fdnavarropecci, thanks for your detailed thought process. I am like you where after I ran "GRANT SELECT ON [lakehouse_demo].[dbo].[Customer10] TO [username];", this [username] could see every single tables and views even though we only granted this [username] one view. Have you found a solution to this yet?

Re: How to Grant Access to an Individual Table/View in SQL Analytics Endpoint for a Lakehouse?

fdnavarropecci — Fri, 09 May 2025 10:34:14 GMT

Hi @PatChan , we tested this again and is now working as expected. We suspect it was related to some role assignments that were removed shortly before granting access to the item.

Re: How to Grant Access to an Individual Table/View in SQL Analytics Endpoint for a Lakehouse?

Kash10 — Thu, 31 Jul 2025 05:27:02 GMT

Hi,

I performed the steps mentioned below but I am getting the below error
User is not authorized to perform current operation for workspace '39d311d6-640e-4655-8757-ab01acca075a', artifact '76085361-7964-470c-a992-3e6d82a64ac8'.

What needs to be done here, if I don't the user to be able to view all the items unders the workspace.

Re: How to Grant Access to an Individual Table/View in SQL Analytics Endpoint for a Lakehouse?

fdnavarropecci — Thu, 31 Jul 2025 07:17:24 GMT

This looks like you're missing permissions to share the item, you need either member or admin role at the workspace level to be able to do so.

Re: How to Grant Access to an Individual Table/View in SQL Analytics Endpoint for a Lakehouse?

Kash10 — Thu, 31 Jul 2025 07:20:50 GMT

Thanks for your response fdnavarropecci!
But I am the platform and the workspace admin, just by following the above steps, I am still not able to provide the permission to view the table data. Is it possible to list all the steps so that i can try them in the same order.