Restore SQL 2022 database to a different server & change owners issue Error: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner

Question

Hi,

We copied/converted AS400 DB2 database to SQL 2022 database serverA and then backup & restore to SQL 2022 databaseB and when we try to change owners using a script (set @oldowner = 'CTL', set @newowner = 'TESTCTL') then it is throwing error.

Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 75 [Batch Start Line 0] Object 'CTL.F00165' does not exist or is not a valid object for this operation. Msg 15151, Level 16, State 1, Line 1

We observed that the SYSOBJECTS and SYSUSERS uid is different.

Please assist.

Thanks.

Answer 1

The SP ist deprecated since version 2000 and may already remove.

Use ALTER AUTHORIZATION instead.

See

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-changeobjectowner-transact-sql?view=sql-server-ver17

Answer 2

I don't want to sound condescending, but it sounds like your knowledge of SQL Server is from the days of SQL 2000 and older. Things have changed since then.

As Olaf said, to change the owner of an object, you should use ALTER AUTHORIZATION, not sp_changeobjectowner.

But I am not sure that you should change the owner at all. Way back in SQL 2000 and older versions, the notation CTL.F00165 indeed mean that CTL was the owner of the object F000165. However, starting with SQL 2005 schema and ownership has been decoupled. So CTL only refers to the schema.

To find out in which schema F00165 is in, you can run this query:

SELECT schema_name(schema_id) FROM sys.objects WHERE name = 'F00165'

Observe the dot after sys. sys.objects and sys.database_principals have replaced the old sysobjects and sysusers that you refer to. The latter still exist, but they are compatibility views and do not expose all information. The same is true for all other old system tables.

As I said, I am not sure that you should change the owner at all. In very many databases, dbo owns everything, and having non-dbo users owning objects has to be considered to be an advanced feature. Maybe it is the right thing in your case, but maybe you are only looking into move the table to a different schema?