Unable to view, delete, or drop an external location in the UI or through commands, even with admin privileges

Use metastore admin rights to change ownership to yourself or the required user, and then manage.

Written by girish.sharma

Last published at: November 4th, 2024

Problem

You are unable to view, delete, or drop an external location in the UI or through commands, despite having admin privileges. This issue yields an error message.

 

UnauthorizedAccessException: PERMISSION_DENIED: User is not an owner of External Location 'sandbox_external_location'. 

 

This issue typically arises in environments where users are migrating to Unity Catalog and need to manage external locations created by other engineers.

 

Cause

In Unity Catalog, only the owner of an external location can see and manage an external location. Metastore admins do not have this permission.

 

Solution

First, ensure you have metastore admin rights. If you don’t, add yourself or the required user to the metastore admin privileges account console under the Catalog section.

Next, as a metastore admin, you can change the owner of the external location to yourself or another required user. Execute the following query to change the owner:

 

ALTER EXTERNAL LOCATION `sandbox_external_location` OWNER TO `<change-to-required-user>`

 

Once the ownership has been changed, the new owner can proceed to delete or drop the external location using the appropriate commands.

 

Note

Additionally, avoid storing production data in sandbox environments to prevent similar issues.