Granting a single User Access to a Single Object in a Lakehouse SQL Endpoint

[andrerodrigues]

Can @Microsoft  or someone help with how to grant a specific user access to a single object, in this case, a view, within a particular schema in the SQL Endpoint of a lakehouse in the workspace, without granting access to anything else, just this view within this schema in the SQL Endpoint.

What we have so far is adding the user as a viewer in "manage access", denying all access to all schemas within the lakehouse using SQL queries, and then granting access to the specific object (the view) to the respective user. 

Could you help us and let us know if there is a simpler or clearer way to grant this access?

[AndyDDC · Accepted solution]

Hi @andrerodrigues you can add the user (or group) to the Lakehouse permissions without checking any of the options.  This adds the user to the Lakehouse permissions as "connect" only.  You can then issue GRANT statements to specify the objects the user (or group) has access to.

--------------------------------------------------------------------------------

If my reply has been useful, please consider providing kudos and marking

as the solution for other community members to find

--------------------------------------------------------------------------------

[frithjof_v · Accepted solution]

Hi @andrerodrigues ,

The box which @AndyDDC shows, can be found this way:

Click the three dots next to the lakehouse name, and then "Manage permissions"

And then "+Add user"

[andrerodrigues]

FYI, this solution does not work.

[frithjof_v]

It has worked for me several times in the past.

What do you mean by "does not work"? What doesn't work?

[Shreya_Barhate · Accepted solution]

The permissions for Lakehouse and SQL endpoints in Microsoft Fabric need to be applied separately because they serve different purposes and have distinct security models.
To provide access to a Single Object in a Lakehouse SQL Endpoint you need share the lakehouse without any additional permission enabled
Then run the GRANT statement for the object you need to provide access for
SQL:

GRANT SELECT ON OBJECT::schemaname.tablename TO [user@domain.com];  

GO

Now the user should be able to view the data from table.

[AndyDDC · Accepted solution]

Hi @andrerodrigues you can add the user (or group) to the Lakehouse permissions without checking any of the options.  This adds the user to the Lakehouse permissions as "connect" only.  You can then issue GRANT statements to specify the objects the user (or group) has access to.

--------------------------------------------------------------------------------

If my reply has been useful, please consider providing kudos and marking

as the solution for other community members to find

--------------------------------------------------------------------------------

[andrerodrigues]

Hello @AndyDDC 
Could you explain where do you access that menu in the Print Screen? Because on my screen, the only menu related with the accesses that I can utilize is this below: 

And using this botton I cannot have those options.

Thank you a lot for the help!

[frithjof_v · Accepted solution]

Hi @andrerodrigues ,

The box which @AndyDDC shows, can be found this way:

Click the three dots next to the lakehouse name, and then "Manage permissions"

And then "+Add user"