問題

在tempdb創建用戶，并授予權限，重啟sql server后發現權限丟失，deny權限卻還在。

原因

在 SQL Server 中，雖然 tempdb 會在每次重啟時重新創建，但并不是所有的用戶權限設置都會被重置。GRANT 和 DENY 權限的行為在 tempdb 上的不同之處主要體現在 SQL Server 內部的權限處理邏輯。

為什么 DENY 權限會保留？

1. 權限的層次與持久性：

• DENY 權限直接修改系統元數據，阻止特定的權限或操作。
• SQL Server 在重啟時會重新初始化 tempdb，但對特定用戶或對象的 DENY 權限仍然會被保存在內存和系統元數據中，即使是臨時數據庫。
• DENY 權限的效果是更為嚴格的權限限制，相比于 GRANT 權限更直接影響用戶權限的檢查順序。

2. DENY 優先級高于 GRANT：

• DENY 權限的優先級高于 GRANT 或 CONTROL，在權限檢查時更容易被保留和優先處理。
• 即便在數據庫重啟后，SQL Server 會優先應用拒絕策略。

3. 系統對象與 tempdb 的初始化：

• tempdb 的重建過程中保留了對系統安全性的控制，如拒絕備份操作的權限，這是為了保持安全性和數據一致性。
• DENY 的權限修改不會隨著 tempdb 的重建被顯式撤銷，因為它直接影響系統權限檢查，而不是在重建時主動移除的設置。

與 CONTROL 權限丟失的對比

• CONTROL 權限：屬于更高級別的權限，允許對數據庫和其對象的全面控制，涉及復雜的訪問控制列表（ACL），這些在 tempdb 重建時不被視為系統必須保留的設置，因此會丟失。
• DENY 權限的持久性：由于 DENY 權限的內置安全特性，它在權限檢查中起到更強的約束作用，因此會被 SQL Server 內部保留，即使在 tempdb 重新創建時也會沿用這些限制。

總結

• DENY 權限在 SQL Server 重啟時仍能保留，是因為它直接作用于安全性層級并且在權限檢查中的優先級較高。
• CONTROL 等 GRANT 權限不被保留，是因為 tempdb 的重建不視這些為默認安全策略的一部分。
• 若需要保持特定權限（如 CONTROL），必須通過啟動過程或作業等機制手動重新設置。

處理

要在 tempdb 重啟后保留權限設置，你需要在 SQL Server 啟動時自動重新應用這些權限。可以通過以下幾種方法實現：

方法 1: 使用 sp_procoption 創建啟動過程

可以創建一個啟動過程，在 SQL Server 啟動時自動運行該過程來重新設置 tempdb 中的權限。

1. 創建存儲過程：定義一個存儲過程來設置 tempdb 中的權限。

USE master;
GO

-- 創建啟動過程，用于在SQL Server啟動時自動授予權限
CREATE PROCEDURE SetTempdbPermissions
AS
BEGIN
    -- 切換到tempdb
    USE tempdb;

    -- 授予root用戶CONTROL權限
    IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'root')
    BEGIN
        GRANT CONTROL TO [root];
    END
END;
GO

2. 配置存儲過程為啟動過程：將上述存儲過程設置為 SQL Server 啟動時自動執行。

-- 將存儲過程設為自動啟動
EXEC sp_procoption 
    @ProcName = 'SetTempdbPermissions', 
    @OptionName = 'startup', 
    @OptionValue = 'on';
GO

方法 2: 使用 SQL Server Agent 作業

另一種方法是創建一個 SQL Server Agent 作業，在 SQL Server 啟動時運行腳本來重新設置權限。

1. 創建 SQL Server Agent 作業：配置作業在啟動時運行授予權限的腳本。
2. 設置作業觸發條件：將作業的觸發條件設置為 SQL Server 啟動時。

方法 3: 手動執行腳本

如果不頻繁重啟 SQL Server，手動運行一個腳本來重新應用權限也是一種簡單可行的方式。

總結

• tempdb 的權限在重啟后會丟失，這是由于其重建特性導致的。
• 使用啟動過程或 SQL Server Agent 作業來自動重新設置權限，是保持權限設置的有效方式。