Perms on Tempdb?

From: Rick (b_at_bt.net)
Date: 01/26/04 

Date: Mon, 26 Jan 2004 15:20:55 -0600

Hey, All,

We need to create temp tables but when we do, we get an error that the user
doesn't have permission to the tempdb database. We are using:

CREATE TABLE #TEMP1
(COL1 INT)
...
If we do this in, say, the Northwind database, the error says something like
'Unable to create table in tempdb' and something about permission denied.
There are no permissions granted in Northwind or tempdb. We can fix the
error problem by granting "create table" on tempdb. Whenever SS restarts
all tempdb perms are lost.

Is there a reason why this happens, as well as a fix?

We're using SS2K and SP3.

Relevant Pages

  • losing permisions in tempdb
    ... I have noticed that everytime sql server restarts the permission on tempdb go away. ... Is there a way to fix this? ... Also i thought this sort of information was stored in the master database? ...
    (microsoft.public.sqlserver.security)
  • Re: use of tempdb by union all
    ... > You are certainly correct about table variables using the tempdb database, ... Andrew J. Kelly SQL MVP "Tenaya" wrote in message ... > You are certainly correct about table variables using the tempdb database,> and not being entirely memory resident ... ...
    (microsoft.public.sqlserver.programming)
  • Re: Error Granting DB / Role Access
    ... the database or object owner (within tempdb) even though they have DBO ... The script below executes correctly. ... >> Now the owner of the tempdb database is SA, and the dbo login is mapped to ...
    (microsoft.public.sqlserver.security)
  • SQL Server 2000: Tempdb growth is causing application failure...
    ... to look at the tempdb as being the problem. ... import of records into the temporary table and aggregiate functions occurs ... dbcc checkalloc ... My thinking is the tempdb database and/or logs are ...
    (microsoft.public.sqlserver.server)
  • Re: use of tempdb by union all
    ... You are certainly correct about table variables using the tempdb database, ... and not being entirely memory resident ... ... are you stating the SQL Server will use the ...
    (microsoft.public.sqlserver.programming)