Pass-Through Query SQL Security

[Guest]

We have an Access application that is actually using an ODBC connection to
change data within a SQL Server database. The Access application utilizes
Pass-Through stored procedures. We thought we could revoke ISUD privileges
and instead use privileges through the Stored Procedures to preform SQL
Server table inserts and updates. we are blowing up with permission errors on
the Access side when trying to update the data.

Does anyone have any ideas as to what's going on here???

I am betting our DBA that if we re-add ISUD privileges to the group, we'll
be ok. His argument is that we shouldn't have to do that since we're running
Pass-Through queries through Access.

Any help would be appreciated.

Thanks in advance for your help.

wnfisba

 

[Van T. Dinh]

I am reasonably sure that you can use SPs for SIUD without explicit
permissions on the Tables. Some relevant extracts from B-O-L:

"The owner of a stored procedure can grant EXECUTE permissions for the
stored procedure. If the owner of a base table wants to prevent users from
accessing the table directly, they can grant permissions on views or stored
procedures referencing the table, but not grant any permissions on the table
itself. This is the foundation of the SQL Server mechanisms to ensure that
users do not see data they are not authorized to access."

and

"Stored procedures, commonly used as an interface to perform complex
activities, can be used to customize security permissions in much the same
way as views.

For example, in an archiving scenario, stored procedures can copy data older
than a specified interval into an archive table and then delete it from the
primary table. Permissions can be used to prevent users from deleting the
rows from the primary table directly or from inserting rows into the archive
table without deleting them from the primary table. You can create a
procedure to ensure that both of these activities are performed together,
and then grant users permissions to execute the procedure."

However, I *think* an (explicit) Deny permission on the base Tables will
prevent the user from doing processing (SIUD) through the since Deny
permission will always take precedence.

Check B-O-L on resolving conflicting permissions ...

Remember that the above only applies to the SP. If, says, the user does
SIUD through a Form bound to an ODBC-linked Table (or a Query based on
ODBC-linked Tables), he/she needs the permissions (SIUD and DRI if
appropriate).

 

[aaron.kempf]

dude WTF

linked Access tables and passthrough; all that CRAP is obsolete

use Access Data Projects
it is a much much much simpler architecture.

No linking refreshing; no horsing around.

Just simple client-server development.

10 times easier in development
10 times easier in administration
10 times stronger security

time to lose the training wheels; WUSSY mvps

 

[Rick Brandt]

We have an Access application that is actually using an ODBC
connection to change data within a SQL Server database. The Access
application utilizes Pass-Through stored procedures. We thought we
could revoke ISUD privileges and instead use privileges through the
Stored Procedures to preform SQL Server table inserts and updates. we
are blowing up with permission errors on the Access side when trying
to update the data.

Does anyone have any ideas as to what's going on here???

I am betting our DBA that if we re-add ISUD privileges to the group,
we'll be ok. His argument is that we shouldn't have to do that since
we're running Pass-Through queries through Access.

Any help would be appreciated.

Thanks in advance for your help.

wnfisba

Are all of the tables used in the stored procedure owned by the same SS user as
the owner of the SP? Are the SP and all of the base tables in the same SQL
Server database? If the answer to either is No then it gets more complicated.
In past updates to plug security holes in SQL Server these rules have changed a
bit.