Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

LorenzoBonati
Microsoft Employee
‎12-02-2025 09:00 AM

Automating SQL Permissions Deployment in Microsoft Fabric Data Warehouse

Over the last few months, I’ve worked on some customer cases where database roles were successfully deployed from Dev to Prod, but the associated SQL permissions like 'GRANT SELECT ON OBJECT::dbo.MyView' did not follow.

 

After digging into this, the behavior is confirmed to be expected by design in the current version of Fabric:

 

  • Deployment Pipelines and Git integration transfer metadata objects (tables, views, stored procedures, and the role definitions themselves).

  • However, SQL engine-level permissions stored in sys.database_permissions are not part of the deployable artifact.

  • As a result, when a Warehouse is deployed, the roles appear in Prod, but their object-level permissions need to be reapplied manually.

 

This has led some customers to spend time troubleshooting something that is simply not included in the deployment model today. So I prepared two SQL scripts to make this easier and more predictable until Fabric adds native support for permission deployment.

 

🔍1. Permissions Pre-Check Script

This script gives you a clear picture of who has explicit permissions in your Warehouse—distinguishing:

 

  • Database roles

  • AAD users and AAD groups

  • Object-level vs schema-level vs database-level permissions

 

It’s useful both as a diagnostic tool and to help you validate whether your deployment process relies on role-based security or direct permissions.

 

📄Script:
👉 https://github.com/LorenzoBonati/microsoft-fabric-scripts/fabric-data-warehouse/permissions-deployme... 

 

📤2. Export SQL Permissions Script (Dev → Prod)

 

This script generates clean SQL statements you can run directly in Prod. It exports:

 

  • Custom role definitions

  • GRANT / DENY permissions (DATABASE, SCHEMA, OBJECT, COLUMN)

  • Role memberships

 

The output is already formatted as idempotent SQL commands, so you can reapply them safely after a deployment.

📄Script:
👉 https://github.com/LorenzoBonati/microsoft-fabric-scripts/fabric-data-warehouse/permissions-deployme... 

 

💡How to Use These Scripts in Your Workflow

 

Step 1 — Run the Pre-Check Script in Dev
See exactly which permissions your Warehouse is using and whether they are role-based or user-based.

 

Step 2 — Run the Export Script in Dev
Copy the generated SQL output. Here one example of result ready to paste from the Fabric SQL Editor UI: 

LorenzoBonati_1-1764432719919.png

 

Step 3 — Apply the output in Prod after each deployment
This restores the role permissions that Fabric does not transfer automatically.

I’ve tested both scripts on multiple Warehouse deployments, and they helped remove guesswork and reduce manual effort for customers who rely heavily on role-based access models.

 

If you have feedback, ideas, or improvements, I’m happy to keep iterating on them.

Thanks for reading, and hopefully this helps anyone dealing with Warehouse permissions in Fabric!

Comments