Changing a table owner

Question

I'm trying to change the owner of a table:

sp_changeobjectowner 'OWNER.TABLENAME', 'dbo'

But when executing I get the error message:

Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 62
Object 'OWNER.TABLENAME' does not exist or is not a valid object for this operation.

Answer 1

The correct way to do this in SQL Server 2005 and up is to stop thinking about the prefix as an "owner." The sp_changeobjectowner procedure has been deprecated since SQL Server 2005, and you should instead be using schema DDL, e.g.:

ALTER SCHEMA dbo TRANSFER [current_owner].tablename;

To check the current "owner" (this may return multiple rows if you have more than one tablename in multiple schemas):

SELECT s.name
  FROM sys.schemas AS s
  INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
  WHERE t.name = N'tablename';

Also be sure that you spell the object correctly. In a case-sensitive collation, for example, TABLENAME and tablename are not the same object, and spelling it with InCorrEcT CaSe could also lead to this error.

Answer 2

Your statement is correct:

EXEC sp_changeobjectowner '<owner>.<tableName>', '<newOwner>' 

If the error happend, try to check who is the current owner of the table:

EXEC sp_table_privileges '<tableName>'  

Answer 3

To cover the case where a table exists within a constructed schema name like 'Common' (that is not related to a username), then it is the schema owner that needs to be changed.

alter authorization on schema::Common TO dbo;

That will change the objects within the schema to the specified owner 'dbo' and keep the table within 'Common'.

To list schema owners:

select db_name() as Db, 
       sch.name as SchemaName, 
       u.Name as Owner
  from sys.schemas sch
  join sys.sysusers u
    on u.uid = sch.principal_id;