How to: change DB owner name using T-SQL

Here is how you can do it using SQL Statement.

Method 1:

Using this method first we’ll get all the db objects and update with new db owner name.

DECLARE tabcurs CURSOR
FOR
    SELECT 'olddbowner.' + [name]
      FROM sysobjects
     WHERE xtype = 'u'

OPEN tabcurs
DECLARE @tname NVARCHAR(517)
FETCH NEXT FROM tabcurs INTO @tname

WHILE @@fetch_status = 0
BEGIN

    EXEC sp_changeobjectowner @tname, 'dbo'

    FETCH NEXT FROM tabcurs INTO @tname
END
CLOSE tabcurs
DEALLOCATE tabcurs

Method 2:

This approach is simple and straight forward. Using this we will build the statements and you can copy them from the result window and execute

declare @OldOwner varchar(100) declare @NewOwner varchar(100) 
set @OldOwner = '353446_eda_db' 
set @NewOwner = 'dbo' 


SELECT 'sp_changeobjectowner ''[' + s.name + '].[' + p.name + ']'', ''' + @NewOwner + '''
 GO'
FROm sys.Procedures p INNER JOIN 
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = @OldOwner
union
select 'sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'', ''' + @NewOwner + '''
 GO'
 from information_schema.tables where Table_schema = @OldOwner

Hope this helps.