SQL Server REVERT 子句實戰：在預存程序中暫時提權並安全返回原執行內容

EXECUTE AS 讓程式碼模組以指定使用者身分執行，REVERT 則將執行內容**切回呼叫者原始身分**。兩者結合可實現「最小權限原則」：平時低權限，僅在必要區塊 (如建立備份、DDL) 暫時提權，隨即恢復。

核心語法

CREATE PROCEDURE dbo.DoCriticalWork
WITH EXECUTE AS OWNER  -- 或 EXECUTE AS 'HighPrivUser'
AS
BEGIN
    -- 低權限區塊：呼叫者原始權限
    SELECT 'Start low-priv work';

    -- 暫時提權區塊
    EXECUTE AS USER = 'BackupOperator';  -- 或 LOGIN
    BEGIN TRY
        BACKUP DATABASE CriticalDB TO DISK = '...';  -- 需高權限
        SELECT 'Backup done';
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE();
    END CATCH
    REVERT;  -- 關鍵：切回呼叫者原始內容

    -- 繼續低權限區塊
    SELECT 'Back to low-priv';
END

實作範例：建立測試帳號與權限

-- 1. 建立低權限使用者 SQLUser1、高權限 DBA SQLDBA
USE master;
CREATE LOGIN [SQLUser1] FROM WINDOWS WITH DEFAULT_DATABASE = AdventureWorks;
CREATE LOGIN [SQLDBA] FROM WINDOWS WITH DEFAULT_DATABASE = AdventureWorks;

USE AdventureWorks;
CREATE USER SQLUser1 FOR LOGIN [SQLUser1];
CREATE USER SQLDBA FOR LOGIN [SQLDBA];
EXEC sp_addrolemember 'db_owner', 'SQLDBA';

關鍵行為

• EXECUTE AS 切換內容後，USER_NAME()、SESSION_USER、ORIGINAL_LOGIN() 反映新身分；ORIGINAL_LOGIN() 始終保持真實登入。
• REVERT 只能切回**上一層** EXECUTE AS 內容，若巢狀呼叫需成對使用。
• 若 EXECUTE AS 指定使用者不存在或無權限模擬，建立模組時即報錯。

應用場景

1. DDL 維護預存程序：呼叫者僅需 EXECUTE 權限，程序內 EXECUTE AS OWNER 執行 CREATE INDEX/ALTER TABLE。
2. 跨資料庫操作：EXECUTE AS LOGIN = 'CrossDBUser' 存取其他資料庫，REVERT 隔離權限範圍。
3. 審計與追蹤：ORIGINAL_LOGIN() 記錄真實操作者，滿足合規要求。

結語

EXECUTE AS + REVERT 是 SQL Server 實現「最小權限、即時提權、自動回收」的核心機制。善用它可將高權限操作封裝在受控模組中，大幅降低權限過度授予風險。