Why does my tempdb reset permissions when the server is rebooted?

Question

The past two times we have rebooted our sql server, our website has gone down. The reason appears to be because the tempdb is getting recreated and the ASPState user is losing permission to read/write to the tempdb (it is an ASP site and session data is stored in the sql server)

This was not a problem until about two weeks ago. Does anyone know how I can prevent the sql server from resetting tempdb permissions after a reboot? Or why this only started happening recently? We are using MS SQL Server 2005.

Answer 1

I know this is an old question but found some new information regarding the tempdb behaviour on restarting. The tempdb is essentially recreated from the 'model' db and that is the reason why all changes to it are lost. If you make a change to persist your changes even after restart make the same changes to the 'model' db as you would to the 'tempdb'. Have a look at the following: Does tempdb Get Recreated From model at Startup?

Answer 2

Here's a script to create a startup stored procedure, which loops over Logins and creates Users in tempdb as db_owner. This script does not have harcoded logins.

As a result even after SQL machine restarts all SQL logins will have privileges to access 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