SQL Server 修改資料表擁有者的兩種高效方法

資料庫重構或權限整理時，常需批量變更資料表的擁有者（Schema）。本文提供單一資料表修改與全資料庫批量修改兩種 T-SQL 方案，滿足不同場景需求。

方法一：單一資料表快速變更

使用系統預存程序 sp_changeobjectowner 即可完成單一物件擁有者變更：

EXEC sp_changeobjectowner 'tablename', 'dbo';

參數說明：第一參數為「原擁有者.資料表名」（如 old_schema.tablename），第二參數為新擁有者（通常為 dbo）。

方法二：批量變更指定擁有者下的所有資料表

建立預存程序 User_ChangeObjectOwnerBatch，透過游標遍歷 sysobjects 系統表，精準篩選並批量執行變更：

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
    @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

使用範例

將 legacy_user 擁有的所有資料表轉移給 dbo：

EXEC dbo.User_ChangeObjectOwnerBatch 'legacy_user', 'dbo';

注意事項

• 執行前請完整備份資料庫，批量操作不可逆。
• 確認新擁有者（Schema）已存在於資料庫中。
• 涉及外鍵、檢視表、預存程序等相依物件時，需同步檢查權限鏈完整性。
• SQL Server 2005+ 建議改用 ALTER SCHEMA ... TRANSFER ... 語法，效能更佳且支援交易回滾。