Skip to main content
idfg-badge

Idaho Fish and Game

Changing the Owner of Tables in SQL Server

idfg-bthomas

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.