问题

在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 作业来自动重新设置权限，是保持权限设置的有效方式。