Allowing access to tempdb

Question

Allowing access to tempdb

Brian Brown-204626

Hall of Fame

Points: 3211
More actions
May 5, 2010 at 3:04 pm

#222222

We are currently in the process of consolidating many in-house apps onto a common set of
DEV-Test-Prod servers and are taking this opportunity to upgrade to SQL Server 2008 SP1.
However, we have hit a snag with one of our apps.
Under SQL Server 2000, anybody could access the tempdb and look in the sysobject table to
find the temporary table names. While they could not access the local temp tables, they could
access the global temp tables. One of our apps, written about 8 years ago, took advantage of
this by creating a global table for each user who logged in to the app, and then placing data in
the table to be used if they needed to debug a problem. We were not aware that they were
doing this, and it really was not a problem while they were on their own dedicate server.
Now, however, under 2005/2008, the tempdb is secured and you cannot see the system or
temporary tables unless you have server level permissions or datareader on the tempdb. Some
of the other apps, which will be using the server, may create global temporary tables containing
confidential information. The user is insisting that they need the access to tempdb to find the
temp table names they have created. But, they will also be able to see any other global tables
that have been created on the server. Also, they will lose permissions on tempdb any time
SQL Server is cycled.
Does anyone have any recommendations about how to allow access to the tempdb for this one
app, while still protecting the other apps data, or any comments in general about this issue?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

Brian Brown-204626 (5/5/2010)
The user is insisting that they need the access to tempdb to find the temp table names they have created.

I guess I don't understand... how would they know they found the names they created unless they already knew the names?

If they just want to verify if the table really does exist or not...

IF OBJECT_ID('TempDB..#temptablename) IS NOT NULL --table exists

BEGIN

yada-yada...

END

IF OBJECT_ID('TempDB..#temptablename) IS NULL --table doesn't exist

BEGIN

yada-yada...

END

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brian Brown-204626 Hall of Fame Points: 3211 More actions · Answer 2

They use the app name and the user login account as part of the table name, but say they don't know the user accounts and have to look for the tables to find the correct one.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

Gosh... I'm not sure how to help. Generally, everyone has access to TempDB and should be able to read TempDB.sys.Objects. I'm also surprised that if the GUI is responsible for making the name that they just don't pass the bloody thing as a parameter.

I know it doesn't help, but it sounds like a very odd system because they're not only using globabl temp tables, but they don't know what the names of those tables are.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)