Script to change ALL Database Objects back to dbo

I’ve had a few instances now were database login / users have been incorrectly configured prior to software installations (or migrated from other environments) and consequently the schema ownership, tables, stored proceedures etc are all prefixed with that specific database username.

This may work fine in the first instance (when everything’s initially setup), however tends to cause problems when the vendor comes back to perform upgrades on the DB, only to have this fail when their scripts try to refer to dbo. database objects which don’t exist.

The below script will go through ALL database objects and change the object owner back to dbo. From there, when a new Windows or SQL login is created for use with this database, either configure it to use the dbo schema as the default, or associate the user directly as the database owner using sp_changedbowner.

/* 
Change ALL database object ownership to dbo, including;
U = user table
V = view
s = system table
p = stored proc 
FN = function
Variables:
%%DATABASENAME%%		- Database Name you're updating
%%CURRENTOBJECTOWNERNAME%%	- CURRENT object owner name
*/
declare @object varchar(517)
declare @dbname nvarchar( 128 )
/* BE SURE TO DECLARE THE CORRECT DATABASE HERE!!!! */
SELECT @dbname = '%%DATABASENAME%%'
declare objects cursor for select name
from sysobjects o
where o.type in ('U','V','S','P','FN') and
convert(sysname,user_name(o.uid)) = '%%CURRENTOBJECTOWNERNAME%%' /* this is the database-USER you are changing FROM */
open objects
while(1=1)
begin
fetch next from objects into @object
if @@fetch_status < 0 break
exec ('sp_changeobjectowner ' + '''' + '%%CURRENTOBJECTOWNERNAME%%' + '.' +  @object + '''' + ', ' + '''' + 'dbo' + '''' )
end
deallocate objects