用存储过程批量修改数据库中对象的所有者

---

经常把本地的sqlserver数据库导入服务器，有时会发生数据库中对象的所有者变化的情况，最简单最快的办法是通过存储过程批量来修改它：
存储过程：
CREATE PROCEDURE 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
OK,保存后通过查询分析器调用执行就可以了。