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
FETCH	curTables INTO @sql 
WHILE 	@@FETCH_STATUS = 0
BEGIN
	IF IsNull( @Debug, "N" ) = "Y"
		SELECT @sql
	ELSE
		EXEC  (@sql)

	FETCH	curTables INTO @sql 
END

CLOSE curTables
DEALLOCATE curTables
DROP TABLE #tables
SET XACT_ABORT ON


GO