User keeps disappearing from Tempdb database

[mazmanit]

We have a web application that requires the use of a specific user listed in USERS in the tempdb database of a SQL 2000 server.

This account will occasionally disappear. I appreciate that the Tempdb is a temporary database, however the account will disappear even thought the server has not been rebooted or failed over.

Is there any processes that can cause the account to disappear, The SA and guest account in tmpdb are not affected.

I have checked the SQL logs and Event logs and there is nothing there to suggest why this is happening

 

[JaxSpace]

The TEMPDB is recreated each time that the SQL Server is restarted. As with all of your other databases, it uses the MODEL database as a template. Therefore, if the user is not defined within the Users of the MODEL database, they won't show up on the TEMPDB.

Rather than inserting a new user into the MODEL database and then have to remove various users from every new database created, you might want to simply create a Stored Procedure that executes each time the server is started and uses GrantDBAccess and AddSrvRoleMember to apply the appropriate access. Just a thought. Good luck.

 

[JaxSpace]

Correction. You can't create a Stored Procedure in one DB (e.g. Master) to affect another DB (e.g. TempDB). Instead, you will need to simply create a Job (under Management) that uses the appropriate DB and appropriate Stored Procedures. Example:

EXEC sp_grantdbaccess 'DOMAIN\User', 'DOMAIN\User'
EXEC sp_addrolemember 'db_owner', 'DOMAIN\User'

You can then click the Schedule tab and schedule this Job everytime that the server is started.