Change owner on all database objects by stored procedure

Another handy dandy script:  https://codebetter.com/blogs/darrell.norton/archive/2004/06/18/16932.aspx

Darrell Norton (MVP) has a script that will allow you change the database owner on all of your databases/objects - super helper script!

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[ChangeAllObjectOwnersToDBO]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP procedure [dbo].[ChangeAllObjectOwnersToDBO]

GO

SET QUOTED_IDENTIFIER OFF    SET ANSI_NULLS  ON

GO

 

CREATE proc ChangeAllObjectOwnersToDBO

AS

SET nocount ON

 

declare @uid int

declare @objName varchar(50)

declare @userName varchar(50)

declare @currObjName varchar(50)

declare @outStr varchar(256)

SET @uid = user_id('dbo')

 

declare chObjOwnerCur cursor static

FOR

SELECT user_name(uid) AS 'username', [name] AS 'name' FROM sysobjects WHERE uid <> @uid

 

open chObjOwnerCur

IF @@cursor_rows = 0

begin

  print 'All objects are already owned by dbo!'

  close chObjOwnerCur

  deallocate chObjOwnerCur

  RETURN 1

end

 

fetch next FROM chObjOwnerCur INTO @userName, @objName

while @@fetch_status = 0

begin

  SET @currObjName = 'dbo.' + @objName

  IF (object_id(@currObjName) > 0)

    print 'WARNING *** ' + @currObjName + ' already exists ***'

  SET @outStr = 'sp_changeobjectowner "' + @userName + '.' + @objName + '", "dbo"'

  print @outStr

  print 'go'

  fetch next FROM chObjOwnerCur INTO @userName, @objName

end

 

close chObjOwnerCur

deallocate chObjOwnerCur

SET nocount off

RETURN 0

 

GO

SET QUOTED_IDENTIFIER OFF    SET ANSI_NULLS  ON

GO