Mischa Kroon

Just got this from someone on irc worked great for me :)

There are a couple of uses for this... hopes it helps someone else as well.

------------------------------------------------------------------
-- 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