成批更改数据库对象的所有者
1 --功能说明：成批更改数据库对象的所有者
2 --作者：不详
3 --用法：exec ChangeObjectOwner 'vs2005','dbo'
4 --即可将所有vs2005所有者的对象改为dbo所有
5 --运行成功后将提示："注意: 更改对象名的任一部分都可能破坏脚本和存储过程。"
6 Create proc ChangeObjectOwner
7 @OldOwner as NVARCHAR(128),--参数原所有者
8 @NewOwner as NVARCHAR(128)--参数新所有者
9 AS
10 DECLARE @Name as NVARCHAR(128)
11 DECLARE @Owner as NVARCHAR(128)
12 DECLARE @OwnerName as NVARCHAR(128)
13
14 DECLARE curObject CURSOR FOR
15 select 'Name' = name,
16 'Owner' = user_name(uid)
17 from sysobjects
18 where user_name(uid)=@OldOwner
19 order by name
20
21 OPEN curObject
22 FETCH NEXT FROM curObject INTO @Name, @Owner
23 WHILE(@@FETCH_STATUS=0)
24 BEGIN
25 if @Owner=@OldOwner
26 begin
27 set @OwnerName = @OldOwner + '.' + rtrim(@Name)
28 exec sp_changeobjectowner @OwnerName, @NewOwner
29 end
30
31 FETCH NEXT FROM curObject INTO @Name, @Owner
32 END
33
34 close curObject
35 deallocate curObject
36 GO
37
38
39
当前平均分: 0.0(0 次打分)
-5-4-3-2-1012345