Useful SQL Script

Below is a script that I use to change the owners of all objects on a database to dbo.

------------------------------------------------------------------
-- Name: dbo.sp_FixObjOwners
-- Auth: Sal Terillo ( )
-- 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_FixObjOwner
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
GO