User permission by default in Fabric database

[AnHell]

Hello,

I will explain my scenario to see if I can get what I need.

I have a workspace with a specific ‘SQL Database’ type warehouse, which was in preview until recently.

In that warehouse, I have created several schemas and several tables assigned to those schemas.

I want a user to be able to access that warehouse only to query the tables in the schemas that I give them permission to.

To do this, it is not necessary to give them permissions in the workspace, and I give them access to the warehouse without selecting any extra options. It says they have Read permissions.

With that alone, the user can already see all my schemas and tables, but I only want them to see certain schemas.

I have tried denying select on all schemas except the one I want them to see, and it works correctly. But the day I have 50 schemas, that will be a problem.

Is there a way to ensure that, when creating a user, they cannot see anything by default and I can tell them which schemas they can access?

Thanks in advance!

[v-saisrao-msft]

Hi @AnHell,

Have you had a chance to review the solution we shared by @AntoineW @tayloramy @Nabha-Ahmed ? If the issue persists, feel free to reply so we can help further.

Thank you.

[Nabha-Ahmed]

Hi @AnHell 

You can’t hide schemas by default in Fabric SQL Database. When a user gets Warehouse Read access, they can see all schemas even if SELECT is denied.
The recommended workaround is to REVOKE SELECT at the database level, then GRANT SELECT only on the schemas you want, ideally via database roles so it scales.
Schema-level visibility control is not fully supported yet.

Best regards 

Nabha ahmed

[tayloramy · Accepted solution]

Hi @AnHell, 

@AntoineW is correct for warehouses, however you are using SQL Database, not a warehouse. 

What you should do is share the SQL database itself, then grant permissions only to specific schemas: Authorization in SQL database - Microsoft Fabric | Microsoft Learn

you can also make database level roles to make permissions management easier. 

SQL Database works very much like SQL Server permissions work. 

Share your SQL database and manage permissions - Microsoft Fabric | Microsoft Learn

Configure granular access control for a SQL database - Microsoft Fabric | Microsoft Learn

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

[AnHell]

Hello,

I finally solved it with an idea that @tayloramy 's post gave me.

I created a script to create users that automatically creates the user and denies select access to all schemas.

I also created a script to create schemas, which creates them and denies select access to all users.

With this, I just have to go to the user's properties and grant select access to the few schemas for which he has permissions.

Thanks for everything!

[AntoineW]

Hi @AnHell, 

For now, no, Fabric Warehouse does NOT support a “deny everything by default” model.

Once a user has Read access to the Warehouse, they can see all schemas, even if you deny permissions.

Microsoft’s official guidance for Warehouse permissions is:

👉Use database roles to group object privileges

You create a role such as:

CREATE ROLE schema_reader;
GRANT SELECT ON SCHEMA::TargetSchema TO schema_reader;
EXEC sp_addrolemember 'schema_reader', 'yourUser';

Workarounds : 

Option 1 : Create multiple Warehouses

If isolation is important:

• Put sensitive schemas into separate Warehouses

• Give users access only to the relevant Warehouse

  This is the cleanest and most secure solution in Fabric today. Warehouses are “cheap” because storage is OneLake (shared).

Option 2 : Use Views + Deny real tables

If you want users to see ONLY what you expose:

1. Create a presentation schema

2. Expose views only

Deny access to the underlying schemas

Option 3 : Continue using DENY but automate it

If you must keep a single Warehouse:

• Generate DENY statements dynamically

• Run them via a pipeline or stored procedure

✔ To restrict access:

• Use DENY (scalable with automation), or

• Use multiple Warehouses to isolate schemas, or

Use views + deny underlying tables.

References : 

- https://learn.microsoft.com/en-us/fabric/data-warehouse/security

- https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-granular-permissions

- https://learn.microsoft.com/en-us/fabric/data-warehouse/share-warehouse-manage-permissions

Hope it can help you ! 

Best regards,

Antoine