SQL Server 批次授權儲存程序與函數：動態腳本自動化 GRANT EXECUTE/SELECT 權限

生產環境數百個儲存程序與函數，需授予特定帳號執行權限。手工逐一 GRANT 不現實。本文提供自動化儲存程序 sp_authorize_right，掃描目錄檢視、排除已授權、動態批次授權，支援儲存程序 (P) 與各類函數 (F)。

需求場景

• 登入 Test 僅需：資料庫 MyAssistant 的 DML 權限 + 所有 SP/函數執行權限
• 禁止 DDL (建表、改 SP、刪索引)
• 10+ 資料庫需同步套用，必須腳本化

前置權限設定 (SSMS 圖形介面)

1. 登入 Test → 屬性 → 伺服器角色：僅勾選 public
2. 使用者對應 → 對應 MyAssistant → 資料庫角色：db_datareader, db_datawriter, public
3. 此時已具備 DML 權限，尚缺 EXECUTE SP/函數權限

核心邏輯：sys.database_permissions 判斷是否已授權

若 SP dbo.sp_diskcapacity_cal 已授權給 Test，sys.database_permissions 會有記錄：

• class = 1 (OBJECT_OR_COLUMN)
• major_id = OBJECT_ID('sp_diskcapacity_cal')
• grantee_principal_id = USER_ID('Test')
• permission_name = 'EXECUTE'

查詢使用者 principal_id：

SELECT principal_id, name FROM sys.database_principals WHERE name IN ('Test', 'Test1');

自動化授權儲存程序完整碼

USE MyAssistant;
GO

SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID('dbo.sp_authorize_right', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_authorize_right;
GO

CREATE PROCEDURE dbo.sp_authorize_right
    @type CHAR(1),   -- 'P' = Stored Procedure, 'F' = Function
    @user SYSNAME    -- 目標使用者
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX), @uid INT;
    
    SELECT @uid = principal_id FROM sys.database_principals WHERE name = @user;
    IF @uid IS NULL
    BEGIN
        RAISERROR('User %s not found in current database.', 16, 1, @user);
        RETURN;
    END

    IF @type = 'P'  -- 儲存程序
    BEGIN
        -- 建立暫存表收集需授權的 GRANT 陳述式
        CREATE TABLE #GrantStmt (SqlText NVARCHAR(MAX));
        
        INSERT INTO #GrantStmt
        SELECT N'GRANT EXECUTE ON ' + QUOTENAME(SCHEMA_NAME(p.schema_id)) + N'.' + QUOTENAME(p.name) + N' TO ' + QUOTENAME(@user) + N';'
        FROM sys.procedures p
        WHERE NOT EXISTS (
            SELECT 1 FROM sys.database_permissions dp
            WHERE dp.class = 1 
              AND dp.major_id = p.object_id
              AND dp.grantee_principal_id = @uid
              AND dp.permission_name = 'EXECUTE'
        )
        AND p.is_ms_shipped = 0;  -- 排除系統程序

        -- 列印預覽
        SELECT * FROM #GrantStmt;

        -- 游標逐條執行
        DECLARE cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT SqlText FROM #GrantStmt;
        OPEN cur;
        FETCH NEXT FROM cur INTO @sql;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN TRY
                EXEC (@sql);
            END TRY
            BEGIN CATCH
                PRINT 'FAILED: ' + @sql + ' | ' + ERROR_MESSAGE();
            END CATCH
            FETCH NEXT FROM cur INTO @sql;
        END
        CLOSE cur; DEALLOCATE cur;
        DROP TABLE #GrantStmt;
    END
    ELSE IF @type = 'F'  -- 函數
    BEGIN
        CREATE TABLE #GrantStmt (SqlText NVARCHAR(MAX));

        -- 標量/表值函數 (FN, IF, TF, AF, FS, FT) → GRANT EXECUTE
        INSERT INTO #GrantStmt
        SELECT N'GRANT EXECUTE ON ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) + N' TO ' + QUOTENAME(@user) + N';'
        FROM sys.all_objects o
        WHERE o.type IN ('FN', 'IF', 'TF', 'AF', 'FS', 'FT')
          AND o.schema_id = SCHEMA_ID('dbo')
          AND NOT EXISTS (
              SELECT 1 FROM sys.database_permissions dp
              WHERE dp.class = 1 AND dp.major_id = o.object_id
                AND dp.grantee_principal_id = @uid AND dp.permission_name = 'EXECUTE'
          )
          AND o.is_ms_shipped = 0;

        -- 內嵌/多語句表值函數 (IF, TF) 亦需 SELECT 權限以供 FROM 子句使用
        INSERT INTO #GrantStmt
        SELECT N'GRANT SELECT ON ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) + N' TO ' + QUOTENAME(@user) + N';'
        FROM sys.all_objects o
        WHERE o.type IN ('IF', 'TF')
          AND o.schema_id = SCHEMA_ID('dbo')
          AND NOT EXISTS (
              SELECT 1 FROM sys.database_permissions dp
              WHERE dp.class = 1 AND dp.major_id = o.object_id
                AND dp.grantee_principal_id = @uid AND dp.permission_name = 'SELECT'
          )
          AND o.is_ms_shipped = 0;

        SELECT * FROM #GrantStmt;

        DECLARE cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT SqlText FROM #GrantStmt;
        OPEN cur;
        FETCH NEXT FROM cur INTO @sql;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN TRY
                EXEC (@sql);
            END TRY
            BEGIN CATCH
                PRINT 'FAILED: ' + @sql + ' | ' + ERROR_MESSAGE();
            END CATCH
            FETCH NEXT FROM cur INTO @sql;
        END
        CLOSE cur; DEALLOCATE cur;
        DROP TABLE #GrantStmt;
    END
    ELSE
    BEGIN
        RAISERROR('Invalid @type. Use P (Procedure) or F (Function).', 16, 1);
    END
END;
GO

使用方式

-- 授權所有自訂儲存程序給 Test
EXEC dbo.sp_authorize_right @type = 'P', @user = 'Test';

-- 授權所有自訂函數給 Test
EXEC dbo.sp_authorize_right @type = 'F', @user = 'Test';

-- 批次處理多資料庫 (在每個資料庫建立此 SP 後執行)
-- 可寫成 PowerShell / SQLCMD 腳本迴圈跑多庫

關鍵設計亮點

1. 冪等性：NOT EXISTS 排除已授權物件，重複執行不報錯、不重複授權
2. 安全性：QUOTENAME 防止物件名/使用者名注入；is_ms_shipped = 0 排除系統物件
3. 可觀測性：先 SELECT * FROM #GrantStmt 列印預覽，再執行；失敗捕獲列印錯誤不中斷
4. 效能：LOCAL FAST_FORWARD READ_ONLY 游標開銷極低
5. 擴充性：易擴充支援 VIEW (GRANT SELECT)、TYPE (GRANT REFERENCES) 等

擴充建議

• 加入 @schema 參數支援非 dbo 結構描述
• 加入 @dry_run BIT = 1 參數，僅列印不執行
• 輸出授權日誌表記錄執行時間、物件、使用者、結果
• 整合至 CI/CD 流程，部署後自動同步權限基線