Script to change multiple table owners

Script to change multiple table owners

This script will generate a second script that will change the owner of all tables that are owned by an account that you specify. You must be a member of the sysadmin server role, the db_owner database role, or a member of both the ddl_admin and ddl_securityadmin database roles to change object owners.

·        In Query Analyzer, change the “Execute Mode” to “Results in Text”. CTRL + T.

·        Make sure you are connected to the correct database – the one whose objects you wish to change.

·        Copy the script below and paste into Query Analyzer.

       declare @OldOwner varchar(100)

       declare @NewOwner varchar(100)

       set @OldOwner = 'OldOwner'

       set @NewOwner = 'NewOwner'

       select 'sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'', ''' + @NewOwner + '''

       go' 

       from information_schema.tables where Table_schema = @OldOwner

·        Change the values of @OldOwner and @NewOwner to fit your situation.

·        Play the script.

·        You results should looks something like this:

       sp_changeobjectowner '[OldOwner].[table1]', 'NewOwner'

       go

       sp_changeobjectowner '[oldOwner].[table2]', 'NewOwner'

       go

·        
      Copy the resulting script into a Query Analyzer window connected to the correct database.

·        Play the resulting script to change the owners.

Conclusion

Keep in mind that objects may be owned by users other than dbo when you least expect it. Be sure to explicitly state the owner when creating scripts. If a software package install creates objects in a database, you may want to run the installation with an account with elevated privileges.