写个循环,对所有的视图进行修改不就行了吗?declare @ViewName varchar(250) declare #views cursor for select name from sysobjects where objectproperty(id,N'IsView')=1 order by name open #views fetch next from #views into @viewname while @@fetch_status=0 begin print '更新: '+@viewname exec sp_changeobjectowner @viewname,'aa' --更改视图的所有者,aa是指你要改成的所有者,根据你的需要调整. if @@error<>0 print @viewname+' 更新失败' fetch next from #views into @viewname end close #views deallocate #views
这个不仅能改表的所有者,还能改视图存储过程等对象的所有者,一次性的!CREATE PROCEDURE dbo.ChangeObjectOwner @OldOwner as NVARCHAR(128),--参数原所有者 @NewOwner as NVARCHAR(128)--参数新所有者 ASDECLARE @Name as NVARCHAR(128) DECLARE @Owner as NVARCHAR(128) DECLARE @OwnerName as NVARCHAR(128)DECLARE curObject CURSOR FOR select 'Name' = name, 'Owner' = user_name(uid) from sysobjects where user_name(uid)=@OldOwner order by nameOPEN curObject FETCH NEXT FROM curObject INTO @Name, @Owner WHILE(@@FETCH_STATUS=0) BEGIN if @Owner=@OldOwner begin set @OwnerName = @OldOwner + '.' + rtrim(@Name) exec sp_changeobjectowner @OwnerName, @NewOwner end FETCH NEXT FROM curObject INTO @Name, @Owner ENDclose curObject deallocate curObject GO
declare #views cursor for select name from sysobjects
where objectproperty(id,N'IsView')=1 order by name
open #views
fetch next from #views into @viewname
while @@fetch_status=0
begin
print '更新: '+@viewname
exec sp_changeobjectowner @viewname,'aa' --更改视图的所有者,aa是指你要改成的所有者,根据你的需要调整.
if @@error<>0 print @viewname+' 更新失败'
fetch next from #views into @viewname
end
close #views
deallocate #views
@OldOwner as NVARCHAR(128),--参数原所有者
@NewOwner as NVARCHAR(128)--参数新所有者
ASDECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by nameOPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end FETCH NEXT FROM curObject INTO @Name, @Owner
ENDclose curObject
deallocate curObject
GO