SQL Server 修改資料庫物件擁有者為 dbo 完整指令

適用場景

資料庫遷移、合併、權限整理時，非 dbo 擁有的物件（資料表、檢視、程序、函數）導致權限鏈斷裂、程式碼需加結構描述前綴、維護困難。批次轉移擁有者至 dbo 可統一管理。

核心指令（SQL Server 2000/2005 相容寫法）

-- 允許系統目錄更新（舊版需求，新版已移除此選項）
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE

-- 批次更新 sysobjects uid 為 1 (dbo)
UPDATE sysobjects SET uid = 1 WHERE uid <> 1

-- 還原設定
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE

現代安全做法（SQL Server 2005+）

不建議直接更新系統表（已不可直接修改）。改用 ALTER AUTHORIZATION 或 ALTER SCHEMA ... TRANSFER：

方法 1：ALTER AUTHORIZATION（物件層級）

-- 單一物件
ALTER AUTHORIZATION ON OBJECT::SchemaName.TableName TO dbo

-- 批次產生指令
SELECT 'ALTER AUTHORIZATION ON OBJECT::' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ' TO dbo;'
FROM sys.objects
WHERE principal_id <> 1  -- 非 dbo 擁有
  AND type IN ('U','V','P','FN','IF','TF','TR')

方法 2：ALTER SCHEMA TRANSFER（結構描述層級，推薦）

-- 將非 dbo 結構描述下的所有物件移至 dbo
DECLARE @sql NVARCHAR(MAX) = N''
SELECT @sql += N'ALTER SCHEMA dbo TRANSFER ' + QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name) + N';'
FROM sys.objects
WHERE SCHEMA_NAME(schema_id) <> 'dbo'
  AND type IN ('U','V','P','FN','IF','TF','TR')
EXEC sp_executesql @sql

物件類型代碼對照

執行前必檢

1. 完整備份資料庫
2. 確認無應用程式硬編碼舊結構描述名（如 UserA.Table）
3. 檢查相依物件：sys.sql_expression_dependencies
4. 測試環境先行驗證

常見問題

• 同名物件衝突：dbo 下已有同名表 → 需先重命名或刪除
• 權限遺失：物件層級權限隨擁有者變更而移轉，結構描述權限不變
• 複寫/CDC 啟用資料庫：嚴禁直接改系統表，需依官方文件處理

驗證腳本

SELECT SCHEMA_NAME(schema_id) AS SchemaName, name, type_desc
FROM sys.objects
WHERE SCHEMA_NAME(schema_id) <> 'dbo'
ORDER BY SchemaName, name

結果為空表示全數轉移完成。