D
David W. Fenton
A new user has reported a problem with adding a record to a table in
a SQL Server 2000 database (A2000 front end). This is with code that
has been running successfully with a half dozen other users for
about 18 months. Security is controlled by NTFS security groups
which are made members of custom database roles, and permissions are
applied to objects with those roles. Users don't have any
permissions on the underlying tables, only on views of those tables.
This new user is in exactly the same user groups as other users who
have no problems.
The error is in executing a passthrough query that sets
IDENTITY_INSERT for the current table (Jet is supposed to do this,
but I've found it doesn't -- I look at the SQL trace and I see Jet
sending the command before the SQL INSERT is performed, but if I
don't do it before executing the INSERT, it errors out):
SET IDENTITY_INSERT tblDonor OFF
SET IDENTITY_INSERT tblContributions ON
All of these passthrough queries error out for this user, but work
just fine for all other users having the same permissions.
It's possible that I'm not looking at the right users, and the users
for whom it works are in the groups that end up in the admins
database role, but there are absolutely no permissions set on the
underlying tables for any users whatsoever, so it shouldn't matter
what database roles the user happens to fall into.
I can't figure out why this doesn't work. My guess is that changing
IDENTITY_INSERT is not something that has to do with the actual
tables, but with some level of the database itself, and that somehow
I've managed to run the thing with all the users having Admin
permissions.
Does that sound plausible?
If so, that means I need to run these using server-side stored
procedures instead of trying to use a passthrough. Is that correct?
a SQL Server 2000 database (A2000 front end). This is with code that
has been running successfully with a half dozen other users for
about 18 months. Security is controlled by NTFS security groups
which are made members of custom database roles, and permissions are
applied to objects with those roles. Users don't have any
permissions on the underlying tables, only on views of those tables.
This new user is in exactly the same user groups as other users who
have no problems.
The error is in executing a passthrough query that sets
IDENTITY_INSERT for the current table (Jet is supposed to do this,
but I've found it doesn't -- I look at the SQL trace and I see Jet
sending the command before the SQL INSERT is performed, but if I
don't do it before executing the INSERT, it errors out):
SET IDENTITY_INSERT tblDonor OFF
SET IDENTITY_INSERT tblContributions ON
All of these passthrough queries error out for this user, but work
just fine for all other users having the same permissions.
It's possible that I'm not looking at the right users, and the users
for whom it works are in the groups that end up in the admins
database role, but there are absolutely no permissions set on the
underlying tables for any users whatsoever, so it shouldn't matter
what database roles the user happens to fall into.
I can't figure out why this doesn't work. My guess is that changing
IDENTITY_INSERT is not something that has to do with the actual
tables, but with some level of the database itself, and that somehow
I've managed to run the thing with all the users having Admin
permissions.
Does that sound plausible?
If so, that means I need to run these using server-side stored
procedures instead of trying to use a passthrough. Is that correct?