BUG: Permission Denied When USE TEMPDB Issued from SQL

 Last reviewed: April 28, 1997
Article ID: Q101143

The information in this article applies to:
  • Microsoft SQL Server, version 4.2a
BUG# 8882 (4.2)

SYMPTOMS

If a temporary table created by a user currently exists, then other users, including the System Administrator (SA), will get the following error message from SQL Administrator when issuing 'use tempdb' or choosing 'change database' from the query window: 

   SELECT permission denied on object #temptable_sunfix', database
   tempdb, owner DBO. (msg 229, severity 14, state 2)
NOTE: The error message indicates that the owner of the temporary table is DBO (database owner), which is not true if the temporary table is created by a user.

Example

Log in as a user, then type the following: 

   create table #test(a int)
   go
Leave the connection there, then log in as SA from SQL Administrator, and from query window, type the following: 

   use tempdb
   go
You will then get the error message 229.

WORKAROUND

Use SAF or isql to switch to tempdb. Or use fully qualified names when referring objects in tempdb.

STATUS

Microsoft has confirmed this to be a problem in SQL Administrator version 4.2a. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Additional query words:
Keywords : kbbug4.20 kbtool SSrvSQL_Admin
Version : 4.2 4.2a
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.