熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL語言 >> 正文

成批更改數據庫對象的所有者

2013-11-13 12:37:37  來源: SQL語言 

  功能說明成批更改數據庫對象的所有者

  作者不詳

  用法exec ChangeObjectOwner vsdbo

  即可將所有vs所有者的對象改為dbo所有

運行成功後將提示注意: 更改對象名的任一部分都可能破壞腳本和存儲過程

 

  Create proc ChangeObjectOwner

  @OldOwner as NVARCHAR()參數原所有者

  @NewOwner as NVARCHAR()參數新所有者

  AS

  DECLARE @Name as NVARCHAR()

  DECLARE @Owner as NVARCHAR()

  DECLARE @OwnerName as NVARCHAR()

  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=)

  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 


From:http://tw.wingwit.com/Article/program/SQL/201311/16270.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.