Changing the Owner of Tables in SQL Server

You may configure SQL Server such that users who are not dbo may create tables, but you set yourself up for a world of pain.

Soon, you can no longer do simple table selects (because dbo is inferred) and instead must resort to prefacing every join with [user].[tablename].[fieldname].

The solution is a SQL Server system stored procedure sp_changeobjectowner:

EXEC sp_changeobjectowner 'user.table', 'dbo'

Note that this operation will clear all privileges.

Credit goes to my old co-worker Bruce, who shared this solution many six years ago in an email titled, "Lucky You". I had several hundred tables and views to re-title and privilege at the time.

If using this method with an

If using this method with an SDE Database you will also have to update the [owner] column of the [SDE_column_registry], [SDE_table_registry] and the [SDE_layers] tables to complete the update.