Changing Ownership of Database objects

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