Survival: Sql Server, change ownership on stored procedures

Some time ago I wrote about a couple of methods to grant execution permission on stored procedures, on Sql Server 2000.

Now I had to write something similar, but the scope was to change ownership of the objects; after some googling I found this page on support.microsoft.com that lists a good way to do the job... but I modified slightly the code from MS, because I needed the possibility to decide to actually execute the commands or simply print them.

So I added a simple parameter (the third), datatype bit, default 0 (false). When set to 1 (true) will cause the execution of the commands.

Here's the code:

CREATE PROCEDURE [dbo].[chObjOwner]( @usrName varchar(20), @newUsrName varchar(50), @exec bit = 0)
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 nvarchar(256)       -- SQL command with 'sp_changeobjectowner'
set @uid = user_id(@usrName)

declare chObjOwnerCur cursor static
for
select name from sysobjects
where 1=1
AND uid = @uid
AND xtype in ( 'P', 'U', 'V')
and name <> 'chObjOwner'
-- category: zero is valid for Stored Procedures... but not for tables
-- and category = 0

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
  IF @exec = 1
    execute sp_executesql @outStr
  --print 'go'
  fetch next from chObjOwnerCur into @objName
end

close chObjOwnerCur
deallocate chObjOwnerCur
set nocount off
return 0