SQL Server 變更物件擁有者的程序

轉載網址：http://support.microsoft.com/kb/275312/zh-tw

---

INF：SQL Server 變更物件擁有者的程序
如果您有許多的物件需要變更擁有權，可以使用下列的 SQL Server 預存程序來簡化程序：

if exists (select * from sysobjects where id = object_id(N'[dbo].[chObjOwner]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[chObjOwner]
GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

CREATE proc chObjOwner( @usrName varchar(20), @newUsrName varchar(50))
as
-- @usrName is the current user
-- @newUsrName is the new user

set nocount on
declare @uid int -- UID of the user
declare @objName varchar(50) -- Object name owned by user
declare @currObjName varchar(50) -- Checks for existing object owned by new user
declare @outStr varchar(256) -- SQL command with 'sp_changeobjectowner'
set @uid = user_id(@usrName)

declare chObjOwnerCur cursor static
for
select name from sysobjects where uid = @uid

open chObjOwnerCur
if @@cursor_rows = 0
begin
print 'Error: No objects owned by ' + @usrName
close chObjOwnerCur
deallocate chObjOwnerCur
return 1
end

fetch next from chObjOwnerCur into @objName

while @@fetch_status = 0
begin
set @currObjName = @newUsrName + "." + @objName
if (object_id(@currObjName) > 0)
print 'WARNING *** ' + @currObjName + ' already exists ***'
set @outStr = "sp_changeobjectowner '" + @usrName + "." + @objName + "','" + @newUsrName + "'"
print @outStr
print 'go'
fetch next from chObjOwnerCur into @objName
end

close chObjOwnerCur
deallocate chObjOwnerCur
set nocount off
return 0


GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

使用範例

若要變更所有物件的擁有者由使用者 test 到 dbo，請執行下列程式碼：
exec chObjOwner 'test','dbo'
此程式碼能找出由 test 擁有的所有物件，並且透過使用 sp_changeobjectowner 預存程序產生一系列 SQL 陳述式。您也可以在 SQL Server Query Analyzer 裡檢查及執行此陳述式。

如果新使用者擁有的物件名稱與現在使用者的相同，則警告訊息將會與物件名稱一起顯示。例如，如果 test 及 dbo 都擁有名為 world 的資料表，那下列錯誤訊息將會顯示：
警告 *** dbo.world 已經存在 ***
sp_changeobjectowner 'test.world','dbo'
go
(WARNING *** dbo.world already exists ***
sp_changeobjectowner 'test.world','dbo'
go)
如果試著將依然擁有資料庫物件的使用者卸除，則程序會失敗並產生下列錯誤訊息：
15183 :此使用者擁有資料庫裡的物件，因此無法卸除。
(The user owns objects in the database and cannot be dropped.)