SQL Server Permissions Problem

[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?

 

[Jellifish]

SNIP

If so, that means I need to run these using server-side stored
procedures instead of trying to use a passthrough. Is that correct?

That would be a much better solution than pass-through queries. Not sure
why one user has a problem and the others don't though, are you sure they
all have the same permissions?

 

[David W. Fenton]

That would be a much better solution than pass-through queries.
Not sure why one user has a problem and the others don't though,
are you sure they all have the same permissions?

Well, I had *thought* every non-admin was running with the same
non-admin permissions, but it turns out that the non-admins were not
logging on as themselves! Not because of the database, but because,
er, um, well? I found this out by checking my log of
startup/shutdown (I do this in most of my major apps) and found the
same username being used on more than one workstation.

Why they were doing that, I just don't know. I'm not the sysadmin
for this client (though I am the backup sysadmin), and the user
accounts were properly set up. They just weren't being used.

*sigh*

This app converted very easily without my needing to writing a
single stored procedure. I guess I'll have to pull out my
Chipman/Baron and get to it.

 

[Jellifish]

Well, I had *thought* every non-admin was running with the same
non-admin permissions, but it turns out that the non-admins were not
logging on as themselves! Not because of the database, but because,
er, um, well? I found this out by checking my log of
startup/shutdown (I do this in most of my major apps) and found the
same username being used on more than one workstation.

Why they were doing that, I just don't know. I'm not the sysadmin
for this client (though I am the backup sysadmin), and the user
accounts were properly set up. They just weren't being used.

Just when you thought you'd made it fool-proof someone comes along with a
better fool eh?

)

At least once you have your SPs in place you won't have to maintain the code
in the Access DBs, you can do it all on the server.

 

[Paul Shapiro]

I think you're right about the users having sysadmin role. From SQL 2005 (I
don't have SQL 2000 anymore, but I doubt this changed):

Permissions for SET IDENTITY_INSERT:
User must own the object, or be a member of the sysadmin fixed server role,
or the db_owner and db_ddladmin fixed database roles.

I don't remember testing this, but I also think you would be right that it
should work when a regular user runs a stored procedure that had been
created by a user with those permissions.