This post originated from an RSS feed registered with .NET Buzz by Darrell Norton. Original Post: Change all database object owners to DBO - now runs changeOwner for you Feed Title: Darrell Norton's Blog Feed URL: /error.htm?aspxerrorpath=/blogs/darrell.norton/Rss.aspx Feed Description: Agile Software Development: Scrum, XP, et al with .NET Latest .NET Buzz Posts Latest .NET Buzz Posts by Darrell Norton Latest Posts From Darrell Norton's Blog

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