Ownership change for table using SQL

[Nid-cbs]

It's not possible to use the ALTER TABLE tblname OWNER TO serviceprinc1 command in Azure Databricks, as this isn't supported. I was trying to set a catalog table's ownership, but it resulted in an error. How can I achieve this using a script

[szymon_dybczak]

Hi @Nid-cbs ,

Are you sure that you're using correct syntax? According to documentation it should be:

ALTER TABLE table_name SET OWNER TO principal

[Nid-cbs]

ALTER TABLE catalogue.schema.table OWNER TO GUID

the rror is [PARSE_SYNTAX_ERROR] Syntax error at or near 'OWNER'.(line 1, pos 43)

[szymon_dybczak]

I still think that you're using bad synxtax. You're missing SET keyword before OWNER. Try as below:

ALTER TABLE catalogue.schema.table SET OWNER TO GUID

[Nid-cbs]

Thanks. Still the same error 

[PARSE_SYNTAX_ERROR] Syntax error at or near 'OWNER'.(line 1, pos 35)

[filipniziol]

Hi @Nid-cbs ,

It depends on how you are using the code.
Make sure you are using .sql notebook type or %sql magic command, and not spark.sql.
Also, the owner should be wrapped in ` backtick characters:

%sql
ALTER TABLE test_table SET OWNER TO `owner`

More on that:
https://stackoverflow.com/questions/68804903/databricks-alter-table-owner-to-userid-is-not-working-w...

[Nid-cbs]

it turns out that The ALTER TABLE statement in Databricks SQL does not support changing the owner of a table directly.It seems we need to use REST API to do the same 

[szymon_dybczak]

Thanks for the information. This is very strange though. I'll check it tommorrow. It should work in a notebook with %sql cell.

It won't work if use spark.sql("ALTER TABLE test_table SET OWNER TO `owner_name`") though.

[vjani]

I was getting same error in python notebook and I found typo in my sql:

Changing from

ALTER TABLE table_name SET OWNER TO 'principal'

to below fixed the issue.

ALTER TABLE table_name SET OWNER TO `principal`