为什么我的 tempdb 在服务器重新启动时会重置权限？

Question

过去两次我们重新启动 SQL Server，我们的网站就瘫痪了。原因似乎是因为 tempdb 正在重新创建，并且 ASPState 用户失去了读/写 tempdb 的权限（它是一个 ASP 站点，会话数据存储在 sql server 中），

直到大约两周后这才成为问题前。有谁知道如何防止 sql server 在重新启动后重置 tempdb 权限？或者为什么这种情况最近才开始发生？我们使用的是 MS SQL Server 2005。

Answer 1

首先，您不应该直接向 tempdb 分配权限。由于显而易见的原因，它会在每次重新启动时重新创建。

这实际上提出了一个问题：为什么您需要拥有此数据库的直接权限？

除了能够连接到 sql server 来创建临时表之外，您不需要任何权限。但是，如果您在 tempdb 中创建真实的表，那么我强烈建议您更改此设置以使用专用数据库来实现此目的。

更新
根据马丁的评论，我只能说哇。我从来没有想过这会是一个选择。

好吧，现在我已经从震惊中恢复过来了。

在 sql server 中创建一个按计划执行的新作业。计划应设置为“每当 SQL Server 代理启动时自动启动”。该作业应重新创建必要的 tempdb 权限。

简而言之，当服务器重新启动时，SQL Server 代理将重新启动（前提是服务是这样设置的）。当它重新启动时，它将启动此作业，然后修复您的权限。我预计该站点将仅比 SQL Server 完全重新启动所需的时间多几秒钟。

Answer 2

我知道这是一个老问题，但发现了一些有关重新启动时 tempdb 行为的新信息。
tempdb 本质上是从“model”数据库重新创建的，这就是对它的所有更改都会丢失的原因。如果您进行更改以在重新启动后保留更改，请对“model”数据库进行与“tempdb”相同的更改。
看一下以下内容： tempdb 是否在启动时从模型重新创建？

Answer 3

Model 数据库用作 TempDB 的模板。将用户和权限添加到模型中，并且将在 TempDB 上使用相同的用户和权限。我并不是说这对于每种情况都是最佳解决方案，但它在应用程序需要特定 TempDB 访问的情况下对我有用。

Answer 4

在sql Server上创建启动脚本如下：

use master
go
drop proc AddAppTempDBOwner
go
create proc AddAppTempDBOwner as
declare @sql varchar(200)
select @sql = 'use tempdb' + char(13)
+ 'exec sp_addrolemember ''db_owner'', ''app'''
exec (@sql)
go
exec sp_procoption 'AddAppTempDBOwner', 'startup', 'true'
go 

Answer 5

下面是一个用于创建启动存储过程的脚本，该过程循环登录并在 tempdb 中以 db_owner 身份创建用户。该脚本没有硬编码登录。

因此，即使在 SQL 计算机重新启动后，所有 SQL 登录名也将具有访问 tempdb 的权限。

USE [master]
GO
IF EXISTS ( SELECT *
FROM sysobjects
WHERE  id = object_id(N'AddUsersToTempDb')
    and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE AddUsersToTempDb
END
GO
CREATE PROCEDURE AddUsersToTempDb
AS
DECLARE @loginname as NVARCHAR(100);
DECLARE Login_Cursor CURSOR FOR  
    SELECT loginname
FROM master..syslogins
OPEN Login_Cursor;
FETCH NEXT FROM Login_Cursor INTO @loginname;
WHILE @@FETCH_STATUS = 0  
    BEGIN
    IF (@loginname <> 'sa' AND (NOT @loginname LIKE '##%') AND (NOT @loginname LIKE '%\%'))
    BEGIN
        PRINT @loginname
        IF EXISTS(SELECT * FROM [tempdb].sys.database_principals WHERE type_desc = 'SQL_USER' AND name = @loginname)
        PRINT '  - user already exists'
    ELSE
        BEGIN
            PRINT '  - creating user'
            DECLARE @Sql VARCHAR(MAX)
            SET @Sql =
            'USE Tempdb' + char(13) + 
            'CREATE USER ' + @loginname + ' FOR LOGIN ' + @loginname  + char(13) +
            'EXEC sp_addrolemember db_owner, ' + @loginname
            EXEC (@Sql)
        END
    END
    FETCH NEXT FROM Login_Cursor INTO @loginname;
END;
CLOSE Login_Cursor;
DEALLOCATE Login_Cursor;
GO

EXEC sp_procoption 'AddUsersToTempDb', 'startup', 'true' 
GO

Answer 6

每次服务启动时，SQL Server 中的 tempdb 数据库（根据我读过、听到过或经历过的所有内容）都会完全删除并重新创建。因此，存储在该数据库中或写入该数据库的任何内容（包括角色、用户或其他访问权限设置）都将被清除。除非在实例启动时使用一些繁琐的代码来设置/重置它们，否则我认为您无法解决此问题。 （我不认为模型数据库中设置的任何内容在创建时都会复制到 tempdb，但我从未想过这一点...）

是否有任何此类设置被写入该数据库？您确定您的系统最近没有进行过更改或更新吗？可能相关的是，SQL 实例多久停止并重新启动一次？ （SQL 运行数月甚至数年而不重新启动的情况并不罕见……）