Mirrored Database Table level security for Notebook use

[stellahe102]

Hi,

We have a mirrored database (data is mirrored from a Managed instance SQL Server) in a fabric workspace. We implemented security for users to read certain tables from the mirrored database. User doesn't have access at the workspace or mirrored database level. The user can access it via SQL Endpoint tools like SSMS, but they cannot access the same tables from a Fabric Notebook.

We understand the difference of SQL End point vs lakehouse, but how the access be given at the table level (in mirrored database) so user can access it from notebook? we are exploring ways of copying the mirrored database to a new lakehouse, but that' too much duplication of data, which we try to avoid.

We have searched around this but seems don't have official documents talking about this specfic isssue. Please let us know if we missed anything here.

[Shahid12523 · Accepted solution]

SQL Endpoint → respects table-level security.

Fabric Notebooks → only respect workspace/item-level permissions, not SQL table security.

Result: users need Lakehouse/Mirrored DB item access to use Notebooks.

Workarounds:

Give workspace/item read access.

Copy allowed tables into a separate Lakehouse.

Limitation: table-level security in Notebooks isn’t supported yet (on Microsoft’s roadmap).

[v-sgandrathi]

Hi @stellahe102,

Currently, table-level permissions on mirrored databases in Fabric are only applied when users connect through the SQL Endpoint. When the same data is accessed through Notebooks (Spark), those SQL permissions are not enforced, which is why your users see a difference.

At this time, Fabric does not provide fine-grained table-level security for mirrored databases in Notebooks. The recommended options are:

Run queries in the Notebook through the SQL Endpoint (so your existing SQL permissions apply).

Create views with the required table or row filters and grant users access to those views.

If duplication is a concern, avoid copying into a Lakehouse and use Shortcuts or SQL Endpoint access instead.

The documents below are shared for your review and reference:
Secure mirrored data in Microsoft Fabric database - Microsoft Fabric | Microsoft Learn
Explore Data in Your Mirrored Database With Notebooks - Microsoft Fabric | Microsoft Learn
Secure Mirrored Azure Databricks Data in Fabric with OneLake security  | Microsoft Fabric Blog | Mic...

Thank you.

[v-sgandrathi]

Hi @stellahe102,

Just wanted to check regarding your question. We haven’t heard back and want to ensure you're not stuck. If you need anything else or have updates to share, we’re here to help!

Thank you.

[stellahe102]

@v-sgandrathi 

"Run queries in the Notebook through the SQL Endpoint (so your existing SQL permissions apply)."

•  Can you provide detail on this? within noteboook, I can only run Spark SQL or use pyspark

"Create views with the required table or row filters and grant users access to those views."

• Views are still for SQL end point, user need to access via lakehouse, table level

"If duplication is a concern, avoid copying into a Lakehouse and use Shortcuts or SQL Endpoint access instead."

• for shortcut, tables level access cannot be defined in shortcuts.

Thanks for providing the ideas, so seems the options above doesn't work for the use case here, but let me know if I mis-understand and one of the option actually works for the specific case that: user only access certains tables from notebook.