SQL Change owner of objects

One might need to change the owner of objects in a whole database, especially after a restore. The following are helper stored procedures that one may use to change ownership of a whole set of objects from one owner to another. The list below code are for Routines, Tables and Single items.

Routines

declare @OldOwner varchar(100)
set @OldOwner = 'oldOwner'

declare @NewOwner varchar(100)
set @NewOwner = 'dbo'

select 'sp_changeobjectowner ''[' + routine_schema + '].[' + routine_name + ']'', ''' + @NewOwner + '''go'
from information_schema.routines where routine_schema = @OldOwner

Tables

(take care that Triggers are seperate objects)

declare @OldOwner varchar(100)
set @OldOwner = 'oldOwner'

declare @NewOwner varchar(100)
set @NewOwner = 'dbo'

select 'sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'', ''' + @NewOwner + '''go'
from information_schema.tables where Table_schema = @OldOwner

Single Object

EXEC sp_changeobjectowner 'oldOwner.ObjectName', 'dbo'

Remember: from Query Analyser CTRL-T to do them in text output (not data grid output)

• Tweet
• "&title=""">