Whoever creates a SQL warehouse is defined as the owner by default. There may be times when you want to transfer ownership of the SQL warehouse to another user. This can be done by transferring ownership of Databricks SQL objects (AWS | Azure | GCP) via the UI or the Permissions REST API.

Instructions

Transfer ownership via UI

The ownership of SQL objects can be changed by following the Transfer ownership of a SQL warehouse documentation (AWS | Azure | GCP).

Transfer ownership via API

The public documentation describes how to use PUT requests to change the owner, but this method removes all existing permissions in the warehouse. To preserve the existing permissions, you must perform three specific actions.

1. Get the existing permissions for the warehouse.
2. Parse the response.
3. Make a PUT request to change the owner and append existing permissions.

Sample code (Python)

To run the sample code, you need to copy and paste each section into a notebook cell before running. 

You will need to replace <workspace-name-without-backslash>,  <personal-access-token>,  <warehouse-id>, and <new-owner> with values that are specific to your workspace. <new-owner> is the Databricks username of the person that you want to transfer ownership to.

The sample code is broken up info four key sections. You should complete each section before running the next one.

1.  Setup variables and import required libraries
2.  Create a function to check if a given user exists or not
3.  Get the existing permissions for the SQL warehouse
4.  Verify the new owner exists and update the permissions

Setup variables and import required libraries

import json
import requests

DATABRICKS_HOST = "<workspace-name-without-backslash>"
DATABRICKS_TOKEN = "<personal-access-token>"
WAREHOUSE_ID = "<warehouse-id>"
NEW_WAREHOUSE_OWNER = "<new-owner>"

headers = {
    "Content-Type": "application/json",
    "Accept": "application/json",
    "Authorization": f"Bearer {DATABRICKS_TOKEN}"
}

Create a function to check if a given user exists or not

This function is used later in the code to verify that the NEW_WAREHOUSE_OWNER exists in the workspace. It takes a username as an input value and then verifies it against the existing list of users in the workspace.

### First we check if the user exists in the environment or not.
def doesUserExist(user):  
    response = requests.request(
        "GET",
        f"{DATABRICKS_HOST}/api/2.0/preview/scim/v2/Users?filter=userName+eq+{user}",
        headers=headers
    )
    if "Resources" in response.json():
        return True
    else:
        return False
     

Get the existing permissions for the SQL warehouse

This gets the existing permissions on the SQL warehouse.

### Get existing permissisons
response = requests.request(
    "GET",
    f"{DATABRICKS_HOST}/api/2.0/preview/permissions/sql/warehouses/{WAREHOUSE_ID}",
    headers=headers
)

Verify the new owner exists and update the permissions

• If the new owner exists in the workspace, update the permissions payload, and make a PUT request to apply the new permissions.
• If the new owner doesn't exist in the workspace, print an error message.

if response.status_code != 200:
    raise Exception(f"Failed to get permissions. Response[{response.status_code}]: {response.text}")
    
if doesUserExist(NEW_WAREHOUSE_OWNER):
    print("=== Permission BEFORE change ===\n", response.text)
    existing_permissions = []
    for permission in response.json()["access_control_list"]:
        if (permission["all_permissions"][0]["inherited"] == False):
            if (permission["all_permissions"][0]["permission_level"] == "IS_OWNER"):
                if doesUserExist(permission["user_name"]):
                    existing_permissions.append({"user_name": permission["user_name"], "permission_level": "CAN_MANAGE"})
            else:
                if "user_name" in permission: 
                    key1 = "user_name"
                else:
                    key1 = "group_name"
                existing_permissions.append(
                    {
                    key1: permission[key1],
                    "permission_level": permission["all_permissions"][0]["permission_level"],
                    }
                )
    existing_permissions.append({"user_name":NEW_WAREHOUSE_OWNER , "permission_level": "IS_OWNER"})
    
### Make PUT request to change owner and apply existing permissions
    payload = json.dumps({"access_control_list": existing_permissions})
    response = requests.request(
        "PUT",
        f"{DATABRICKS_HOST}/api/2.0/preview/permissions/sql/warehouses/{WAREHOUSE_ID}",
        headers=headers,
        data=payload
    )
    if response.status_code != 200:
        raise Exception(f"Failed to change permissions. Response[{response.status_code}]: {response.text}")
    print("=== Permission AFTER change ===\n", response.text)
    
else:
    print(NEW_WAREHOUSE_OWNER + " doesn't exists due to which permission cannot be changed")