Darrell Norton's Blog [MVP]

This SQL Server script comes to you courtesy of Geoff Appleby!

Change All Object Owners to a given account (usually dbo) if not already owned by the SA (change the @NewOwner variable to another username string if you don't want DBO to own everything):

 1 declare @ObjectName varchar(256)
 2 -- we are only interested in USER Objects
 3 -- not already owned by 'sa'
 4 -- we don't want keys and constrainst
 5 set @ObjectName = (
 6   select top 1 [name] from sysobjects
 7   where uid <> SUSER_SID('sa')
 8   and [type] in ('FN','IF','P','TF','U','V')
 9   )
10 declare @ObjectOwner varchar(256)
11 declare @ObjectFullName varchar(512)
12 declare @NewOwner varchar(256)
13   set @NewOwner = 'dbo'
14  
15   -- default to 'dbo' if null
16   set @NewOwner = isnull(@NewOwner, 'dbo')
17  
18 while @ObjectName is not null
19 begin
20   select @ObjectOwner = USER_NAME(uid) 
21     from sysobjects where [name] = @ObjectName
22   set @ObjectFullName = @ObjectOwner + '.' + @Objectname
23   PRINT 'Changing ownership of ''' + @Objectname + 
24     ''' from ''' + @ObjectOwner + ''' to ''' + 
25     @NewOwner + ''''
26   execute sp_changeobjectowner @ObjectFullName, @NewOwner
27   set @ObjectName = (select top 1 [name] from sysobjects
28     where uid <> SUSER_SID('sa') 
29     and [type] in ('FN','IF','P','TF','U','V'))
30 end