SQL Server Performance

X not a valid user in database 'tempdb'

Discussion in 'Getting Started' started by digidol, Jun 22, 2007.

  1. digidol New Member

    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.


  2. alzdba Member

    - 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.

  3. digidol New Member

    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.

  4. alzdba Member

    I suppose the temporary table is created like

    create #temptable (....)

    or select ...
    into #temptable
    from ....
  5. Adriaan New Member

    Normally, all logins on a server automatically have appropriate permissions on tempdb ...
  6. digidol New Member

    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?

  7. thomas New Member

    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.
  8. digidol New Member

    Thanks. I will look at this on Monday when I get access to the machine.
  9. ViktorLindholm New Member

    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
  10. satya Moderator

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

Share This Page