01.if exists (select * from sysobjects where id = object_id(N'[dbo].[chObjOwner]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
02.drop procedure [dbo].[chObjOwner]
03.GO
04.
05.SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
06.GO
07.
08.CREATE proc chObjOwner( @usrName varchar(20), @newUsrName varchar(50))
09.as
10.
11.
12.
13.set nocount on
14.declare @uid int
15.declare @objName varchar(50)
16.declare @currObjName varchar(50)
17.declare @outStr varchar(256)
18.set @uid = user_id(@usrName)
19.
20.declare chObjOwnerCur cursor static
21.for
22.select name from sysobjects where uid = @uid
23.
24.open chObjOwnerCur
25.if @@cursor_rows = 0
26.begin
27. print 'Error: No objects owned by ' + @usrName
28. close chObjOwnerCur
29. deallocate chObjOwnerCur
30. return 1
31.end
32.
33.fetch next from chObjOwnerCur into @objName
34.
35.while @@fetch_status = 0
36.begin
37. set @currObjName = @newUsrName + "." + @objName
38. if (object_id(@currObjName) > 0)
39. print 'WARNING *** ' + @currObjName + ' already exists ***'
40. set @outStr = "sp_changeobjectowner '" + @usrName + "." + @objName + "','" + @newUsrName + "'"
41. print @outStr
42. print 'go'
43. fetch next from chObjOwnerCur into @objName
44.end
45.
46.close chObjOwnerCur
47.deallocate chObjOwnerCur
48.set nocount off
49.return 0
50.
51.
52.GO
53.SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
54.GO
