SQL Server 修改資料表擁有者為 dbo 的三種實戰方法

問題背景

備份/還原或跨環境遷移後，使用者建立的表擁有者可能變為原登入帳號 (如 usera)，導致新環境下查詢報「物件不存在」或「使用者已存在」。統一改為 dbo 可消除此類問題。

方法一：系統預存程序 sp_changeobjectowner (推薦)

-- 單張表
EXEC sp_changeobjectowner 'usera.TableName', 'dbo';

-- 預存程序
EXEC sp_changeobjectowner 'usera.ProcName', 'dbo';

-- 批次腳本 (查詢產生腳本)
SELECT 'EXEC sp_changeobjectowner ''' + USER_NAME(uid) + '.' + name + ''', ''dbo'';'
FROM   sysobjects
WHERE  uid <> USER_ID('dbo') AND type IN ('U','P','V');

• 適用於 SQL Server 2000/2005/2008/R2/2012+。
• 僅修改中繼資料，極快、無鎖定、可線上執行。

方法二：企業管理器 / SSMS 圖形化操作

1. 右鍵資料表 →「設計」。
2. 工具列點選「條件約束」圖示 (或右鍵→「檢查約束」)。
3. 切換至「資料表」頁籤，下拉「擁有者」選 dbo。
4. 儲存。

適合少量物件、偏好 GUI 的 DBA。

方法三：直接更新系統表 (極度不推薦，僅供理解)

EXEC sp_configure 'allow updates', 1; RECONFIGURE WITH OVERRIDE;
UPDATE sysobjects SET uid = 1 WHERE uid <> 1;
EXEC sp_configure 'allow updates', 0; RECONFIGURE WITH OVERRIDE;

• 需啟用系統表更新 (高風險)。
• **只能執行一次**；第二次會把 dbo 自己的物件也改壞。
• Microsoft 明確不支援，任何升級/修補可能導致資料庫損壞。

附錄：變更資料庫擁有者

EXEC sp_changedbowner 'dbo';

最佳實踐

1. 開發規範：建表腳本明確指定 AUTHORIZATION dbo 或預設登入為 dbo 角色成員。
2. 遷移腳本：部署後自動跑批次 sp_changeobjectowner 修正。
3. 定期巡檢：SELECT USER_NAME(uid), name FROM sysobjects WHERE uid <> 1;