Change Owner of an Object in SQL Server

So I have this custom app that was built by an unknown a few years ago and it is mission critical! Oh and great news; it assigns ownership to db objects with create login! Now a new user needs to have the account turned over to them and nobody knows who supports this app. I figured it out but it took some digging and it turns out that it is as simple as changing object ownership. Now, who has a valid script to do this in MSSQL 2000?  There are many scripts out there to change the object owner for MSSQL. I have found issues with each one that I have tried so I cleaned up some scripts for MSSQL 2005-2008 and 2000 and posted them here:

CREATE SP - Exchange Object Owner

IF exists (select * from sysobjects where id = object_id(N'[dbo].[sp_ExchangeOwner]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_ExchangeOwner]

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON

GO

CREATE proc sp_ExchangeOwner( @usrName varchar(20), @newUsrName varchar(50))

as

-- @usrName is the current user

-- @NewUsrName is the new user

set nocount on

declare @uid int                   -- UID of the user

declare @objName varchar(50)       -- Object name owned by user

declare @currObjName varchar(50)   -- Checks for existing object owned by new user

declare @outStr varchar(256)       -- SQL command with 'sp_changeobjectowner'

set @uid = user_id(@usrName)

declare chObjOwnerCur cursor static

for

select name from sysobjects where uid = @uid

open chObjOwnerCur

if @@cursor_rows = 0

begin

  print 'Error: No objects owned by ' + @usrName

  close chObjOwnerCur

  deallocate chObjOwnerCur

  return 1

end

fetch next from chObjOwnerCur into @objName

while @@fetch_status = 0

begin

  set @currObjName = @newUsrName + "." + @objName

  if (object_id(@currObjName) > 0)

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

  set @outStr = "sp_changeobjectowner '" + @usrName + "." + @objName + "','" + @newUsrName + "'"

  print @outStr

  print 'go'

  fetch next from chObjOwnerCur into @objName

end

close chObjOwnerCur

deallocate chObjOwnerCur

set nocount off

return 0

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON

GO

---

MSSQL 2000 Version

if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_ExchangeOwner]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_ExchangeOwner]

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON

GO

CREATE proc sp_ExchangeOwner( @usrName varchar(20), @newUsrName varchar(50))

as

-- @usrName is the current user

-- @newUsrName is the new user

set nocount on

declare @uid int                   -- UID of the user

declare @objName varchar(50)       -- Object name owned by user

declare @currObjName varchar(50)   -- Checks for existing object owned by new user

declare @outStr varchar(256)       -- SQL command with 'sp_changeobjectowner'

set @uid = user_id(@usrName)

declare chObjOwnerCur cursor static for

select name from sysobjects where uid = @uid

open chObjOwnerCur

if @@cursor_rows = 0

begin

  print 'Error: No objects owned by ' + @usrName

  close chObjOwnerCur

  deallocate chObjOwnerCur

  return 1

end

fetch next from chObjOwnerCur into @objName

while @@fetch_status = 0

begin

  set @currObjName = @newUsrName + "." + @objName

  if (object_id(@currObjName) > 0)

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

  set @outStr = "sp_changeobjectowner '" + @usrName + "." + @objName + "','" + @newUsrName + "'"

  print @outStr

  print 'go'

  fetch next from chObjOwnerCur into @objName

end

close chObjOwnerCur

deallocate chObjOwnerCur

set nocount off

return 0

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON

GO