Delete query is passing a select statement to my SQL2000 server database. Why?

[Tanzen]

The background is I have a table on a sql2000 server that allows an
Active Directory group select, update, insert, and delete rights on
the index column. However, select rights are denied on all other
columns for security reasons. Users can only update, insert, or delete
values in those columns, they can not view the columns.

I have tested a basic delete query from Query analyzer on SQL 2000
server to this table impersonating a user with the permissions
outlined above, and the delete query works fine. However, when I run
the same query from my MS Access 2003 database logged in as the same
user, it fails with this error:

---------------------------
ODBC --call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Select permission
denied on column 'TestData' of object 'zztesttable',
database 'Passwords_IT", owner 'dbo'. (#230)

---------------------------

Here is the query I am running in Access:

DELETE zztesttable.ID
FROM zztesttable
WHERE (((zztesttable.ID)=4));

So I ran a SQL Profile trace on what Access is sending to the SQL
server to cause this SELECT permission error and sure enough, when I
run this query, here is what SQL is receiving:

SELECT "ID","TestData" FROM "dbo"."zztesttable" WHERE "ID" = @P1

The problem is obvious. The SELECT is not allowed on the "testdata"
column (only on the ID column) and so SQL returns an error and never
gets to the DELETE query. If I edit the table permissions to allow
SELECT statements on all columns, this is what SQL Profiler shows:

First the Select statement is passed from access: SELECT
"ID","TestData" FROM "dbo"."zztesttable" WHERE "ID" = @P1
Second the Delete statement arrives and is passed: DELETE FROM
"dbo"."zztesttable" WHERE "ID" = @P1

So what I don't understand is why is Access sending a select statement
when what I ran was a Delete query? If anyone wants to take this on, I
can send much more detailed trace files. I just didn't want to
overload this post until I know what information will be useful.

 

[Tanzen]

The background is I have a table on a sql2000 server that allows an
Active Directory group select, update, insert, and delete rights on
the index column. However, select rights are denied on all other
columns for security reasons. Users can only update, insert, or delete
values in those columns, they can not view the columns.

I have tested a basic delete query from Query analyzer on SQL 2000
server to this table impersonating a user with the permissions
outlined above, and the delete query works fine. However, when I run
the same query from my MS Access 2003 database logged in as the same
user, it fails with this error:

---------------------------
ODBC --call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Select permission
denied on column 'TestData' of object 'zztesttable',
database 'Passwords_IT", owner 'dbo'. (#230)

---------------------------

Here is the query I am running in Access:

DELETE zztesttable.ID
FROM zztesttable
WHERE (((zztesttable.ID)=4));

So I ran a SQL Profile trace on what Access is sending to the SQL
server to cause this SELECT permission error and sure enough, when I
run this query, here is what SQL is receiving:

SELECT "ID","TestData" FROM "dbo"."zztesttable" WHERE "ID" = @P1

The problem is obvious. The SELECT is not allowed on the "testdata"
column (only on the ID column) and so SQL returns an error and never
gets to the DELETE query. If I edit the table permissions to allow
SELECT statements on all columns, this is what SQL Profiler shows:

First the Select statement is passed from access: SELECT
"ID","TestData" FROM "dbo"."zztesttable" WHERE "ID" = @P1
Second the Delete statement arrives and is passed: DELETE FROM
"dbo"."zztesttable" WHERE "ID" = @P1

So what I don't understand is why is Access sending a select statement
when what I ran was a Delete query? If anyone wants to take this on, I
can send much more detailed trace files. I just didn't want to
overload this post until I know what information will be useful.

Just so people know, I decided to move to using stored procedures.
Much easier and then I didn't have to worry about running delete
queries from within access. I just run the pass-through query to call
a stored procedure that has the delete query built in and then it
works fine.