批量更新存储过程所有者

一。首先，添加如下存储过程

CREATE PROCEDURE dbo.ChangeObjectOwner

 @OldOwner as NVARCHAR(128)='FoodKingdom',--参数原所有者

 @NewOwner as NVARCHAR(128)='dbo'--参数新所有者

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

 

二。然后，执行以下语句

exec ChangeObjectOwner '要更改的名称','dbo'