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.