change ownership of a single object
EXEC sp_changeobjectowner 'authors', 'Muhammed'
Bulk Table Change Owner:
This will change the ownership of all tables found under a specified owner
DECLARE @oldOwner sysname, @newOwner sysname, @sql varchar(1000)
SELECT@oldOwner = 'aspalliance'
, @newOwner = 'dbo'
, @sql = 'IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @oldOwner + '''
)EXECUTE sp_changeobjectowner ''?'', ''' + @newOwner + ''''
EXECUTE sp_MSforeachtable @sql
Bulk Stored Procedure Change Owner
Since there's no direct iteration method for stored procedures so the code below will produce the queries for each stored procedure, copy the produced text and run it in query analyzer.
DECLARE @oldOwner sysname, @newOwner sysname
SELECT @oldOwner = 'aspalliance' , @newOwner = 'dbo'
select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+''''
from
INFORMATION_SCHEMA.ROUTINES a
where
a.ROUTINE_TYPE = 'PROCEDURE'
AND a.SPECIFIC_SCHEMA = @oldOwner
AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)), 'IsMSShipped') = 0
