Script to Change Stored Procedure Owner to DBO

CREATE   Procedure dbo.change_storedprocedure_owner_to_dbo

@Debug char(1) =  NULL

AS
DECLARE @sql VARCHAR(4000)

SET NOCOUNT ON
SET XACT_ABORT OFF 

CREATE TABLE #tables
( UserName  varchar(1000)
, ProcName  varchar(1000)
, ProcID    int
)



SET@sql = "INSERT #tables SELECT su.NAME UserName, so.Name ProcName, so.id ProcID " +
" FROM" + db_name() + "..sysobjects so inner join " + db_name() + "..sysusers su " +
" ON so.uid = su.uid " +
" WHERE type = 'P'" +
" AND so.uid <> 1"

EXEC( @sql )


DECLARE curTables CURSOR
FOR
SELECT  "exec sp_changeobjectowner '" + UserName + "'.'"+ ProcName + "', dbo"
FROM#tables
ORDER BY ProcName 

OPEN curTables
FETCHcurTables INTO @sql 
WHILE @@FETCH_STATUS = 0
BEGIN
IF IsNull( @Debug, "N" ) = "Y"
SELECT @sql
ELSE
EXEC  (@sql)

FETCHcurTables INTO @sql 
END

CLOSE curTables
DEALLOCATE curTables
DROP TABLE #tables
SET XACT_ABORT ON


GO