SQL permissions function fails in tempdb
From: james (james_at_discussions.microsoft.com)
Date: 10/07/04
- Next message: boon: "Re: Question on C2 Log"
- Previous message: Trek Fan: "Application and Data Security"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 6 Oct 2004 16:25:01 -0700
I have noticed that the function “permissions” in SQL Server does not work
for tables in tempdb. This also causes the system stored procedures
sp_tables and sp_columns to fail when used in tempdb.
I suspect this is actually a deeper issues than just the “permissions”
function, likely the actual permissions of temp tables and the fact that they
are all owned by dbo.
I’m curious if anyone else has come across this or knows anything about it.
To see the problem, run the script below for a regular user and run it for
sa (or another system administrator level user). Open two sessions in Query
Analyzer, one for sa and one for a regular user. Set both databases to
tempdb, run the scripts and compare the results.
CREATE TABLE #t
(
low int,
high int
)
--regular user returns 0 indicating no permissions, admin returns 4096
indicating SELECT permissions for the table
select permissions (o.id)&4096, permissions(o.id), * from sysobjects o where
name like '#t_%'
--both regular user and sa can select from their table
select * from #t
In addition to that, when you create a temp table as a regular user, then
check the permissions from an sa connection, it indicates the sa user has
privileges to read the table, even though that table cannot be read from the
sa connection. In the sa session, check the permission of the table created
by the regular user. It will show 4096 indicating permissions to select from
the table. Try to select from the table and you will get an error because
even sa does not have permissions to read a temporary table created by
another user. No one has access to temporary tables except the connection
that made them.
My reason for asking about this is that a third-party software package we
use has started calling sp_tables to validate tables and it fails because the
tables are in tempdb.
- Next message: boon: "Re: Question on C2 Log"
- Previous message: Trek Fan: "Application and Data Security"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
