Script para mudar o dbowner de todos objetos

Declare @OldOwner sysname,
@NewOwner sysname

SET @OldOwner = ‘oldOwner’
SET @NewOwner = ‘dbo’

DECLARE CURS CURSOR FOR
SELECT name FROM sysobjects WHERE type = ‘U’
AND
uid = (SELECT uid FROM sysusers WHERE name = @OldOwner)
AND
NOT name LIKE ‘dt%’ FOR READ ONLY

DECLARE @ProcName sysname

OPEN CURS

FETCH CURS INTO @ProcName

WHILE @@FETCH_STATUS = 0
BEGIN
IF @@VERSION >= ‘Microsoft SQL Server 2005′
BEGIN
EXEC(‘alter schema ‘ + @NewOwner + ‘ transfer ‘ + @OldOwner + ‘.’ + @ProcName)
exec(‘alter authorization on ‘ + @NewOwner + ‘.’ + @ProcName + ‘ to schema owner’)
END
ELSE
EXEC(’sp_changeobjectowner ”’ + @OldOwner + ‘.’ + @ProcName + ‘”,”‘ + @NewOwner + ‘”’)

FETCH CURS INTO @ProcName
END

CLOSE CURS
DEALLOCATE CURS

Observação: Ao utilizar este script, altere as aspas simples e duplas, pois ao salvar o script utilizando o editor do wordpress ele acabou reformatando as mesmas.

Rodolfo