SQL Server Schema – How to Transfer Table Ownership Using sp_changeobjectowner

schemasql serversql-server-2017

I have a number of tables in the database that belong to schema FOO, at least I think they do; they appear in the tables listing in SSMS as FOO.tablename, and when I right click on one of them and choose Properties, FOO appears in the Schema field, and the hint for the Schema field at the bottom of the Properties window says "The schema that contains the table".

However, when I try to make FOO the owner of a table, of dbo.MyTable, say, like so:

        exec sp_changeobjectowner MyTable, [FOO]

I get this error:

Database principal or schema 'FOO' does not exist in this database.

Why is my schema being rejected as non-existent? Obviously, I'm not understanding something about schemas in SQL Server.

I believe this database existed in an earlier version of SQL Server and was restored into SQL Server 2017.

I want to move a table not in the schema into the schema so it has the schema prefix.

Best Answer

sp_changeobjectowner is a deprecated procedure:

Important

This stored procedure only works with the objects available in Microsoft SQL Server 2000 (8.x). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER SCHEMA or ALTER AUTHORIZATION instead. sp_changeobjectowner changes both the schema and the owner. To preserve compatibility with earlier versions of SQL Server, this stored procedure will only change object owners when both the current owner and the new owner own schemas that have the same name as their database user names.

To transfer an object to a different schema, use ALTER SCHEMA:

ALTER SCHEMA schema_name   
   TRANSFER [ <entity_type> :: ] securable_name   
[;]  
  
<entity_type> ::=  
    {  
    Object | Type | XML Schema Collection  
    }
Related Question