Tempdb Permissions Problems
From: Gary Harding (nospam_at_please.com)
Date: 02/04/05
- Next message: Hamelech Al Hakol: "Re: SQL question: How to periodically read the last 'n' rows of a simple table?"
- Previous message: Alejandro Mesa: "RE: How to get values from SELECT stmt into local variables?"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 4 Feb 2005 20:31:42 -0000
Hello, we're using SQL Server 2000 on Windows Server 2003 and XP Pro.
I've read of problems with non-admin users using tempdb (see KB Q243023),
and now we've hit a related but more insidious problem using ADO/MDAC 2.8.
I thought I'd post the details here to see if it rang any bells.
It seems that column metadata for temporary tables is not properly returned
if the
current user is not a member of the Administrators group. That means that
if you
create a temporary table with a primary key, then select from that table
into
an ADO Recordset, the Recordset field for the primary key column will not
have
the correct information in its KEYCOLUMN property (its value is False).
This causes problems for us when we update fields from the temporary table
and call rs.UpdateBatch, at which point we get a failure with "Insufficient
key
column information for updating or refreshing". This must be because ADO
can't find a key column in the Recordset metadata to serve as a row
identity.
This "feature" took a while to find, since the error doesn't occur for admin
users.
It seems very odd that ADO can get the data from the table but not the key
column information.
The workaround, as with Q243023, is to run the following at every startup:
exec tempdb..sp_addrolemember 'db_owner','guest'
Regards
Gary
- Next message: Hamelech Al Hakol: "Re: SQL question: How to periodically read the last 'n' rows of a simple table?"
- Previous message: Alejandro Mesa: "RE: How to get values from SELECT stmt into local variables?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
