Make DBO the owner of all procs, tables, views

------------------------------------------------------------------
-- Name: dbo.sp_FixObjOwners
-- Auth: Sal Terillo (sterillo@hotmail.com)
-- Date: 10/1/2002
-- finds objects that are not owned by dbo
-- and changes them to dbo
-- this eliminates the problem with objects accidentally
-- created as being owned by someone other than dbo
-- REVS:
--
------------------------------------------------------------------

CREATE   PROC dbo.up_FixObjOwners 
AS
SET NOCOUNT ON

DECLARE @dynsql varchar(1000)
SET @dynsql = ''

DECLARE @Obj_Owner sysname
SET @Obj_Owner = ''

DECLARE @Obj_Type VARCHAR(30)
SET @Obj_Type = ''

DECLARE @Obj_Name sysname
SET @Obj_Name = ''

DECLARE @ObjCounter INT
SET @ObjCounter = 0

DECLARE @DBO CHAR(3)
SET @DBO = 'DBO'

-- temp table to hold all objects not owned
-- by DBO
create table #ChangeOwners(
	id int identity(1,1),
	Obj_Owner sysname,
	Obj_Name sysname,
	Obj_Type varchar(30))
-- populate it
INSERT #ChangeOwners (Obj_Owner, Obj_Name, Obj_Type)
select
	su.name, 
	so.name, 
	case 
		when type = 'u' then 'table'
		when type = 'p' then 'sproc'
		when type = 'v' then 'view'
	end as obj_type
from sysusers su
join sysobjects so
on su.uid = so.uid
where su.name not in ('information_schema', 'dbo')
and so.type in ('p', 'u', 'v')
-- select * from #ChangeOwners

SET @ObjCounter = @@rowcount	-- holds the count of rows inserted into #ChangeOwners

WHILE @Objcounter > 0
   BEGIN
	-- construct string for object ownership change
	SELECT @Obj_Name = Obj_Owner + '.' + Obj_Name FROM #ChangeOwners WHERE id = @ObjCounter
	SELECT @Obj_Type = Obj_Type FROM #ChangeOwners WHERE id = @ObjCounter

	SET @dynsql = 'sp_ChangeObjectOwner ''' + @Obj_Name + ''', ' + @DBO
	--select @dynsql
	print 'changing ownership on ' + @Obj_Type + ': ' + @Obj_Name 
	EXEC(@dynsql)
	SET @ObjCounter = @ObjCounter - 1
   END

-- ok all done, collect garbage
drop table #ChangeOwners