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.Temporary tables are just that. Temporary.
The user should have Public access to the database.
From Books Online:
tempdb is re-created every time SQL Server is started so the system starts
with a clean copy of the database. Because temporary tables and stored
procedures are dropped automatically on disconnect, and no connections are
active when the system is shut down, there is never anything in tempdb to
be saved from one session of SQL Server to another.
Temporary tables are automatically dropped when they go out of scope,
unless explicitly dropped using DROP TABLE:
A local temporary table created in a stored procedure is dropped
automatically when the stored procedure completes. The table can be
referenced by any nested stored procedures executed by the stored procedure
that created the table. The table cannot be referenced by the process which
called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of
the current session.
Global temporary tables are automatically dropped when the session that
created the table ends and all other tasks have stopped referencing them.
The association between a task and a table is maintained only for the life
of a single Transact-SQL statement. This means that a global temporary
table is dropped at the completion of the last Transact-SQL statement that
was actively referencing the table when the creating session ended.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We understood all of that.
It doesn't explain why we're getting a permissions error.
Anyone?
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:L5mdhtG5DHA.1988@.cpmsftngxa07.phx.gbl...
quote:
> Temporary tables are just that. Temporary.
> The user should have Public access to the database.
> From Books Online:
> tempdb is re-created every time SQL Server is started so the system starts
> with a clean copy of the database. Because temporary tables and stored
> procedures are dropped automatically on disconnect, and no connections are
> active when the system is shut down, there is never anything in tempdb to
> be saved from one session of SQL Server to another.
> Temporary tables are automatically dropped when they go out of scope,
> unless explicitly dropped using DROP TABLE:
> A local temporary table created in a stored procedure is dropped
> automatically when the stored procedure completes. The table can be
> referenced by any nested stored procedures executed by the stored
procedure
quote:
> that created the table. The table cannot be referenced by the process
which
quote:|||Rick,
> called the stored procedure that created the table.
>
> All other local temporary tables are dropped automatically at the end of
> the current session.
>
> Global temporary tables are automatically dropped when the session that
> created the table ends and all other tasks have stopped referencing them.
> The association between a task and a table is maintained only for the life
> of a single Transact-SQL statement. This means that a global temporary
> table is dropped at the completion of the last Transact-SQL statement that
> was actively referencing the table when the creating session ended.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
Crazy questions:
1. Is there a startup stored procedure that (for example) removes 'unwanted'
rights from tempdb (and other databases)?
2. Has your copy of the 'model' database been altered?
Russell Fields
"Rick" <b@.bt.net> wrote in message
news:401667d8$0$49107$8f4e7992@.newsreade
r.goldengate.net...
quote:
> We understood all of that.
> It doesn't explain why we're getting a permissions error.
> Anyone?
>
> "Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
> news:L5mdhtG5DHA.1988@.cpmsftngxa07.phx.gbl...
starts[QUOTE]
are[QUOTE]
to[QUOTE]
> procedure
> which
them.[QUOTE]
life[QUOTE]
that[QUOTE]
rights.[QUOTE]
>
No comments:
Post a Comment