Transfer ownership of Fabric Warehouse to associated identit

Recently I stumbled across a newly introduced Fabric API that lets you transfer the lake house ownership from User Identity to associated identity (Service Principal or Managed Identity ) but the limitation of this API is that at the moment this identity change currently applies only to Lakehouse and Eventstream.

You can find the details of those API's here.

Due to the limitation of this API , it is not possible to transfer warehouse ownership from User Identity to MI or SPN however through Fabric UI its possible to transfer the ownership from one User Identity to another with the Take over option that you see in the screenshot below.

But there is a workaround through which you could transfer the ownership level access to a SPN or a MI for a Fabric warehouse.

Please note that the following steps are also applicable to SPN's that are Workspace Identities.

If you have ever worked with SQL Server you probably might be aware of the db_ownerrole. Though technically not an ownership but this role has the same privileges as the object/database owner. i.e. dropping the database, creating users in the database, altering the database properties etc. for a user that is a member of the db_role on the database.

The member user of this role will continue to have ownership level access even though the original warehouse user ownership login is disabled, expired or deleted.

Unfortunately , unlike Azure SQL DB, its not possible to create EXTERNAL USERS for Fabric warehouses. That's a major roadblock but there is a workaround for this as well.

Before we move forward, please ensure that you are well aware on how to login into fabric warehouse through SSSM through SPN credentials .

For those details you can go through my LinkedIn post on the topic. This would help to test the role access on the SPN.

I would also highly recommend reading my article on lakehouse permissions.

To get started , chose an SPN either from your Azure or Entra account.

The SPN I am using is Fabric MSAL and the ClientID of this SPN is db5baae3-3155-4744-bfb7-5291acfa1ec3.

Above, I mentioned the ClientID because it makes it easier to validate the process in most of the screenshots in the article.

In the next step, grant the Vieweraccess to this SPN on the workspace where the warehouse exists.

I granted the the Viewer access to a workspace called Medallion Architecture on the Fabric tenant.

Alternatively, you could also grant the Read Data access through Manage permission option on the lake house.

Next, use a login that at least has a Contributor access on the workspace to connect through SSMS.

I logged in to the Fabric workspace using my Microsoft Entra ID MFA credentials through SSMS.

We now have to add the SPN Fabric MSAL as a member of db_owner role on the warehouse DW_Sales.

I used the following command in SSMS to add the SPN Fabric MSAL to the db_owner role.

Alter Role db_owner Add Member [Fabric MSAL]

Ensure that you select the correct database name while executing the above statement.

Once succeeded, the SPN is now a member of the db_owner role on the warehouse DW_Sales.

Although the SPN is a member of the db_owner role, the user identity that originally created it will still be displayed as the owner.

In the next step to test the login, login into SSMS through the SPN. You can refer to my post here on how to do it.

Ensure that while you login through SPN, you type in the database on which the SPN has been added as the member of the db_ownerrole.

You can use the SPN client secrets for the password.

After logging in, you will only see the warehouses for which you are a member of any database role irrespective of the role type.

In this case, as the SPN Fabric MSAL has access only to theDW_SALES warehouse, only that warehouse visible in SSMS .

This effectively grants the SPN Fabric MSAL full access to the warehouse making it the de facto owner. Even if the original user identity is disabled, deleted or expired the warehouse remains accessible through this SPN.

Although the SPN has workspace viewer access, but as its also a member of the db_owner role, the db_owner role access takes precedence there by overriding the viewer (read-only) permissions.

Check the screenshot below. I was able to create a new table by login through the SPN Fabric MSAL which would had not been possible with just workspace Viewer access.

Also, I was able to successfully add the same SPN as a member of the db_owner role for the lakehouse Bronze_Layer on its SQL Analytics endpoint by following the above steps.

Remember DDL statements cannot be executed on the lake houses, so the create table statement used earlier for warehouse would not work for a lakehouse though the db_owner is granted to the Fabric MSAL SPN.

Conclusion

The above approach can be used as a temporary workaround until a robust, full proof and reliable solution is available for effectively handling the warehouse ownership transfer to associated identities.

Thanks for reading !!!