Read/Write Access to Lakehouse alone in Ms Fabric

[prathijp]

I have five lakehouses in my Microsoft Fabric workspace, along with other resources such as pipelines, dataflows, notebooks, etc. I need to:

• Grant read-only access to specific users for one lakehouse.
• Grant read-write access to other users for the same lakehouse.

I tried sharing the lakehouse by enabling the SQL endpoint, but when users attempted to connect via SSMS, they encountered an error - "Login failed for user '<token-identified principal>'. (Microsoft SQL Server, Error: 18456)". However, when I assigned them the Contributor role at the workspace level, they were able to connect successfully via SSMS and query the tables.

If users are added as Members or Viewers to the workspace, they can access the workspace but cannot see the lakehouse tables.

I need guidance on:

1. How to provision minimum permissions for read-only users so they can access lakehouse data via SSMS or OneLake.
2. How to enable write permissions for selected users to create or insert tables in a specific lakehouse.
3. What is the least privileged setup to allow data access through OneLake without granting full workspace-level roles.

[v-tejrama]

Hi @prathijp ,

Thank you @tayloramy  for the response provided!

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[tayloramy · Accepted solution]

Hi @prathijp,

You can do this with item-level permissions on the lakehouse (no workspace role required for readers) and a different path for writers. Here’s the practical setup I’ve used.

1. Read-only via SSMS (T-SQL)
   In the lakehouse, select … > Manage permissions.
   Share the item and add the users (or a group).
   On the SQL analytics endpoint permissions, grant ReadData. This is the specific right that lets SSMS/TDS clients read tables. Microsoft: Lakehouse sharing and permissions
   Have them connect from SSMS using the lakehouse’s SQL endpoint connection string (from Settings > SQL endpoint) with Microsoft Entra (AAD) auth. What is the lakehouse SQL analytics endpoint?, Find the SQL endpoint connection string
2. Read-only via OneLake (files/Spark)
   If they also need file/folder access (Parquet/Delta in Files), grant ReadAll on the lakehouse or create folder-scoped OneLake data access roles for least-privilege read to specific folders. Lakehouse sharing and permissions, Get started with OneLake security
3. Write access (insert/create)
   The lakehouse SQL analytics endpoint is read-only for tables (you can create views/functions and apply SQL security, but no INSERT/CREATE TABLE via SSMS). Writers must use Spark/Notebooks/Pipelines targeting the lakehouse, which requires a workspace role (Contributor/Member) — or use a Warehouse if you want DDL/DML over T-SQL. SQL endpoint is read-only, Workspace roles in Lakehouse

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.