萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL Server 資料庫物件擁有者批次變更:sp_changeobjectowner 與系統表直改法

SQL Server 物件擁有者變更完整指南:從單表到全庫批次、從標準預存程序到系統表直改

虛擬主機遷移、Schema 整理、權限重構時,常需將資料庫物件 (Table, View, SP, Function) 擁有者從舊用戶 (如 `nmkspro`) 統一變更為 `dbo`。本文提供三種方案:系統預存程序、游標批次、系統表直改 (高風險)。

方案一:系統預存程序 sp_changeobjectowner (標準、安全)

單一物件

EXEC sp_changeobjectowner '舊擁有者.物件名', '新擁有者';
-- 範例
EXEC sp_changeobjectowner 'nmkspro.Orders', 'dbo';

批次修改全庫物件 (游標版)

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
            PRINT 'Changed: ' + @OwnerName + ' -> ' + @NewOwner
        END
        FETCH NEXT FROM curObject INTO @Name, @Owner
    END
    
    CLOSE curObject
    DEALLOCATE curObject
END
GO

-- 執行
EXEC ChangeObjectOwner 'nmkspro', 'dbo'

超簡單一鍵批次 (不可文檔化但廣泛使用)

-- 修改所有表
EXEC sp_MSforeachtable 'EXEC sp_changeobjectowner ''?'', ''dbo'' '

-- 修改所有視圖
EXEC sp_MSforeachview 'EXEC sp_changeobjectowner ''?'', ''dbo'' '

-- 修改所有預存程序
EXEC sp_MSforeachproc 'EXEC sp_changeobjectowner ''?'', ''dbo'' '

方案二:系統表直改 (極高風險,僅限極端情況)

-- ⚠️ 高風險操作,需 sysadmin,建議備份 master 後操作
SP_CONFIGURE 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

-- 將所有非 dbo 擁有者的物件改為 dbo (uid=1)
UPDATE sysobjects SET uid = 1 WHERE uid <> 1
GO

SP_CONFIGURE 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

⚠️ 系統表直改風險警示

  • **繞過所有完整性檢查**,可能留下孤立參考、權限不一致
  • **不觸發 DDL 觸發器**,事件通知失效
  • **系統表結構變更** (版本升級/補丁) 可能導致此法失效
  • **Microsoft 不支援**此操作,出問題無官方支援
  • **SQL Server 2005+ 已移除 `allow updates` 選項**,此法在新版**完全不可用**

方案三:SSMS 圖形化介面 (適合少量物件)

  1. 展開資料庫 → 資料表 → 右鍵目標表 → 設計
  2. 屬性視窗 (F4) → 擁有者 (Schema) 下拉選擇 `dbo`
  3. 儲存 (Ctrl+S)

同樣適用於 View、SP、Function (在對應節點右鍵設計/屬性)。

遷移場景最佳實踐

  1. 開發/測試環境先行驗證**:完整跑一次變更腳本,執行應用測試套件
  2. 生產環境維護窗口**:停止應用流量,資料庫單用戶模式執行
  3. 權限同步**:變更擁有者後,檢查並修正 GRANT/DENY 權限是否正確繼承
  4. 應用程式連線字串**:確認不依賴 `user.table` 格式 (應一律用 `dbo.table` 或無 Schema 前綴)
  5. 同步/複寫/鏡像**:變更前暫停,變更後重新初始化

驗證腳本

-- 檢查非 dbo 擁有者物件
SELECT 
    SCHEMA_NAME(schema_id) AS Owner,
    type_desc AS Type,
    name AS ObjectName
FROM sys.objects
WHERE SCHEMA_NAME(schema_id) <> 'dbo'
ORDER BY Owner, Type, name;

-- 檢查特定 Schema 物件數
SELECT SCHEMA_NAME(schema_id) AS Owner, type_desc, COUNT(*)
FROM sys.objects
GROUP BY SCHEMA_NAME(schema_id), type_desc
ORDER BY Owner;

總結

SQL Server 物件擁有者變更:
✅ **首選**:`sp_changeobjectowner` + `sp_MSforeachtable/view/proc` 批次,標準、安全、支援所有版本
⚠️ **次選**:SSMS 圖形化,適合少量手工操作
🚫 **禁用**:`sysobjects.uid` 直改,高風險、不支援新版、違反支援政策
📋 **驗證**:變更前後對比 `sys.objects` Schema 分佈,應用端全量回歸測試

copyright © 萬盛學電腦網 all rights reserved