SQL Server Performance Forum – Threads Archive

X not a valid user in database ‘tempdb’

I have an issue with SQL Server 2000. I am taking over maintenance of a set of databases at different sites. Each site has multiple databases, with different users controlling each database. The application has been working for several months, but error reports have started in the last few weeks. One site is reporting an error that states the user for the database is ‘not a valid user in database ‘tempdb’. I have been able to get access to the problem system. As a temporary solution, I found that adding a user for a specific database to the access list for tempdb allowed the application using the database to continue. (The user is a SQL user rather than a windows account). Therefore, this appears to address the problem. However, if this is to be done for all users there are over a 100 accounts to add access to tempdb. I wondered if there is a more straightforward fix. Is it better practice to use a role to assign multiple users access to a particular resource in SQL Server? From what I have read today, it appears that the public role might be what I need. I don’t have access to the server today so I cannot check if the public role is assigned. Would this be the sensible next step to investigate? Can anyone suggest the situations where a users access to tempdb would be revoked? The sys admins for the machine report no changes to the setup. So, I am not sure why SQL Server previously allowed access. Thanks.

– are there "regular" tables created in tempdb [?] – If yes : keep in mind tempdb is cleared when sqlserver restarts !! [B)] – If you’d need global temporary tables just use ##temptb naming convention.

Thanks for the reply. The tempdb is used for temporary tables only. The error appears to be generated when a stored procedure in the main database for the application creates a temporary table. Other queries that don’t use the tempdb run ok.

I suppose the temporary table is created like create #temptable (….) or select …
into #temptable
from ….

Normally, all logins on a server automatically have appropriate permissions on tempdb …

Yes, the temp tables are created with the # in front of the table name. I thought that all logins are granted access to tempdb, but for some reason this particular machine has lost the setting. Is the correct way to correct this to check that the public role is assigned to tempdb?

You need to ensure that the guest user is present in model and tempdb. All users connecting to the server get access to tempdb via the guest user and tempdb is recreated from model each time sql server starts, so it needs to be in both.

Thanks. I will look at this on Monday when I get access to the machine.

Hi!
I experienced the same problem after a restart of the SQL Server 2000 machine. Did you come up with a solution to the problem?
Best regards
Viktor

Welcome to the forum!
What level of Service pack on SQL you have?

]]>