May 5, 2010 at 3:04 pm
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?
May 5, 2010 at 3:25 pm
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
May 5, 2010 at 3:28 pm
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.
May 6, 2010 at 6:01 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply