更新数据库中表、存储过程或者所有对象的所有者

--更新数据库中所有表的所有者
exec sp_msforeachtable 'sp_changeobjectowner ''?'',''dbo'''

--更新数据库中所有对象的所有者
CREATE   PROCEDURE dbo.ChangeObjectOwner
@OldOwner as   NVARCHAR(128),--参数原所有者
@NewOwner as   NVARCHAR(128)--参数新所有者
AS   

DECLARE @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 name

OPEN 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
END   

close curObject
deallocate curObject
GO


--更新数据库中存储过程的所有者
CREATE   PROCEDURE ChangeProcOwner
@OldOwner as   NVARCHAR(128),--参数原所有者
@NewOwner as   NVARCHAR(128)--参数新所有者
AS   

DECLARE @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 and xtype='p'
order   by name

OPEN 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
END   

close curObject
deallocate curObject
GO   

--exec ChangeProcOwner 'User','dbo'