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