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.
