Well, I'm quite sure that SQL Anywhere (up to v12.0.1) does not allow to alter the owner of a database object at all, i.e. to do something like
ALTER TABLE User1.Table1 RENAME User2.Table1;
ALTER SCHEMA User2 TRANSFER User1.Table1;
Furthermore, I remember to have read several NNTP discussions on that in the past, but I can't find an official statement there nor in this nice forum nor in the docs.
Where does it tell that the only way to "change ownership" is by dropping and recreating the object for the new owner?
I cannot recall seeing this explicitly documented, Volker. But you are correct that, currently, SQL Anywhere does not permit an ALTER statement to change the owner of a table.
answered 13 Aug '12, 21:51
Of course the 'work-around' (I've done this on numerous occassions...) is:
select * into NewOwner.TableName from OldOwner.TableName
or ... if you have lots of indexes & triggers .... or for views, functions, and procedures ....
Unload the database into a script using dbunload, find the 'CREATE <object>' statement ...
and paste and run the modified version with the new owner .. then drop the old object!
answered 14 Aug '12, 07:30
This should be a Product Suggestion. This has been a common request over the years, often from folks coming from Transact SQL who mistakenly thought "dbo" as an owner was a good idea (it's not, for a plethora of reasons, not the least of which is that it's a user id in SQL Anywhere but some other incomprehensible thing in Transact SQL).
FWIW the unfortunately-named PostgreSQL has ALTER TABLE name SET SCHEMA new_schema which is kind of cheesy as syntax goes ("it's an owner, not a "schema", sheesh!) but it apparently does the trick; e.g., it cascades to other objects.
I'll bet money that "cascading" is the reason it hasn't been implemented yet... but having it would be yet another feather in SQL Anywhere's cap, something to make the administrator's life easier that most other products don't have.
answered 14 Aug '12, 17:53