批次變更 SQL Server 資料表擁有者為 dbo：單一指令與游標存儲程序兩法

單一物件變更

EXEC sp_changeobjectowner 'tablename', 'dbo';

批次變更存儲程序

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

使用範例

EXEC User_ChangeObjectOwnerBatch 'old_user', 'dbo';

適用場景

資料庫遷移、還原後擁有者不一致，需統一歸屬 dbo 以簡化權限管理與程式碼引用。