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