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

First off, you shouldn't assign permissions to the tempdb directly. For the obvious reasons that it gets recreated on every reboot.

Which actually raises a question: why do you need to have direct permissions to this database anyway?

You don't need any permissions beyond just being able to connect to sql server in order to create temp tables. However, if you are creating real tables in the tempdb, then I highly suggest you change this to use a dedicated database for this purpose.

UPDATE
Based on Martin's comment all I can say is wow. I would never even have considered that this would have been an option.

Okay, now that I've recovered from the shock.

Create a new job in sql server that executes on a schedule. The schedule should be set to "Start Automatically whenever SQL Server Agent Starts". The job should recreate your necessary tempdb permissions.

In a nutshell, when the server is rebooted the SQL Server Agent will be restarted (provided the service is set that way). When it restarts it will kick off this job that will then fix your permissions. I'd expect the site to remain down for only a few seconds more than it takes for SQL server to completely restart.

Answer 2

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 3

The Model database is used as a template for TempDB. Add users and permissions to model and the same usere and permissions will be used on TempDB. I do not say that this is the optimal solution for every case but it worked for me in a situation where an application needed speciffic TempDB access.

Answer 4

Create a startup script on sql Server as below:

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

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

Answer 6

The tempdb database in SQL server is (from everything I've ever read, heard, or experienced) completely dropped and recreated every time the service is started up. Thus, anything stored within or written to that database, including roles, users, or other access right settings, will be wiped out. Barring some fussy code to set/reset them whenever the instance starts up, I don't think you can work around this. (I don't think anything set in the model database gets copied over to tempdb when it's created, but I've never even thought about that...)

Are any such settings being written to that databases? Are you sure that your system has not been recently changed or updated to do so? Possibly relevant, how often does the SQL instance get stopped and restarted? (It's not uncommon--if not wise--for SQL to run for months if not yers without a restart...)