SQL Server 批量變更資料庫物件擁有者 (dbo) 實用腳本

在虛擬主機或跨環境部署 SQL Server 資料庫時，常因登入帳號不同導致資料庫物件擁有者非 dbo，造成存取權限或程式碼相容性問題。本文提供預存程序與系統預存程式兩種高效批量變更方案，並說明手動修改適用場景。

方案一：自訂預存程序 ChangeObjectOwner（彈性指定新舊擁有者）

CREATE PROCEDURE dbo.ChangeObjectOwner
    @OldOwner NVARCHAR(128),  -- 原擁有者
    @NewOwner NVARCHAR(128)   -- 新擁有者
AS
BEGIN
    DECLARE @Name NVARCHAR(128), @Owner NVARCHAR(128), @OwnerName NVARCHAR(128);
    DECLARE curObject CURSOR FOR
        SELECT name, 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;
END
GO

-- 使用範例：將 nmkspro 擁有的所有物件改為 dbo
EXEC ChangeObjectOwner 'nmkspro', 'dbo';

執行成功會提示：「注意: 更改物件名的任一部分都可能破壞腳本和存儲過程。」屬正常告知，不影響實際變更。

方案二：系統預存程序 sp_MSforeachtable（快速全表歸屬 dbo）

-- 批量修改所有資料表擁有者為 dbo
EXEC sp_MSforeachtable 'EXEC sp_changeobjectowner ''?'', ''dbo'' ';

-- 單一資料表修改
EXEC sp_changeobjectowner '資料表名稱', 'dbo';

sp_MSforeachtable 為未文件化內部程序，雖方便但生產環境建議優先使用方案一，可控性更高。

方案三：企業管理員 / SSMS 圖形介面（適用少量資料表）

1. 右鍵點選資料表 → 設計 → 屬性 → 變更「擁有者」下拉選單。
2. 僅適合資料表數量少（< 20 張）的手動調整。

⚠ 進階方案：直接更新系統表（極高風險，不建議常規使用）

EXEC sp_configure 'allow updates', 1; RECONFIGURE WITH OVERRIDE;
UPDATE sysobjects SET uid = 1 WHERE uid <> 1;  -- 強制歸屬 dbo (uid=1)
EXEC sp_configure 'allow updates', 0; RECONFIGURE WITH OVERRIDE;

警告：直接修改系統基礎表可能導致資料庫損壞、升級失敗或支援中斷，僅限極端情況在完整備份後由資深 DBA 操作。

最佳實務建議

• 開發階段即規範所有物件擁有者為 dbo，避免後續遷移麻煩。
• 部署腳本中加入 EXEC ChangeObjectOwner '舊帳號', 'dbo' 作為標準化步驟。
• 變更後務必執行 DBCC CHECKDB 並驗證應用程式存取正常。