How to grant access for specific tables to e-mail address in Fabric Data Warehouse and Lakehouse

[dinesh01]

How to grant access for specific tables to e-mail address in Fabric Data Warehouse and Lakehouse

[NandanHegde · Accepted solution]

You can use Grant command to give access on specific tables in fabric warehouse:

https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql?view=fabric&preserve-view=...

for lakehouse, you can create a role:

[NandanHegde · Accepted solution]

You can use Grant command to give access on specific tables in fabric warehouse:

https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql?view=fabric&preserve-view=...

for lakehouse, you can create a role:

[Jeanxyz]

It might sound like a stupid question. I have given a user access to a table in Fabric warehouse via the grant command, but as this user doesn't have acess to the Fabric warehouse, how can he/she access the table? I tried the absf path and Power BI desktop - onelake hub, neither works. 

[Anonymous]

Hi @dinesh01 ,

Your solution is great, @NandanHegde . It worked like a charm!

For Fabric Data Warehouse:

Assign workspace roles:

• Go to your workspace and click Manage Access.
• Click Add People or Groups.
• Enter the user's email address.
• Assign the appropriate role based on the level of access you want.

Grant granular permissions:

• Navigate to a specific table in your warehouse.
• Use SQL to set granular permissions for users. This can be done by assigning specific SQL permissions to a table or view.

For more information, please see:

SQL granular permissions - Microsoft Fabric | Microsoft Learn

For Lakehouse:

Share a Lakehouse:

• Navigate to your workspace and select the ellipsis (...) next to the Lakehouse name.
• Select Share from the More Options menu.
• Enter the user's email address and select Grant.

Manage permissions:

• To edit or remove permissions, go to the Lakehouse's direct access screen.
• You can add custom permissions or remove access permissions as needed.

Folder-level access control:

• Open Lakehouse and select Manage OneLake Data Access (Preview).
• Create a new role and assign it to a specific folder in the Lakehouse.

For more information, please see:

Lakehouse sharing and permission management - Microsoft Fabric | Microsoft Learn

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[dinesh01 · Accepted solution]

syntax to give permission on tables and schemas. Below works on both lakehouse and warehouse.

grant select on dbo.table_name to [myname@mail.com]

GRANT select on schema :: schema_name to [myname@mail.com]

[SeattlePowerBI]

Can I use this to grant select access to a onelake security role?