Recently I ran into a problem where all of the table I created had my account as the owner instead of DBO and I wanted a way to change the owner without doing a lot of work (I’m lazy). I also realized it would happen again so I created a script to do it for me. Here it is:

CREATE procedure ChangeTableOwnerToDBO
as

DECLARE @table nvarchar(1024)
DECLARE  mytables CURSOR FOR

SELECT NAME FROM sysobjects WHERE xtype = ‘u’ AND USER_ID() = uid AND uid != 1

OPEN mytables

FETCH NEXT FROM mytables into @table

IF @@FETCH_STATUS = -1
    BEGIN
        PRINT ‘No tables owned by you to reset!’
    END

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT ‘Reassigning owner of ‘ + @table + ‘ to DBO’

        exec sp_changeobjectowner @table, ‘dbo’

        FETCH NEXT FROM mytables into @table
    END

CLOSE mytables
DEALLOCATE mytables