Permissions and ODBC

There are 2 SQL databases involved and an MS Access program... also using
windows authentication...
The Ms Access program executes a stored procedure that resides in SQL
database#1, the stored procedure updates data in SQL dataabase#2..
Assume the user has rights to execute the stored procedure in database#1,
how can you best limit the rights of the user to database#2, but still allow
them to execute the stored procedure in database#1 that updates the data in
database#2 ?
If you have SP3 installed (and you should have), make sure you
understand the rules for cross-database ownership chaining, as
explained in SQL Books Online. You can download the latest version
from http://www.microsoft.com/sql/techinf...000/books.asp.
In general, if you confine all data operations to stored procedures,
you only need to grant Execute permissions on the stored procedures
while revoking or denying select, update, insert and delete
permissions on the base tables/views to the public role so that the
permissions can't be inherited. Creating parameterized stored
procedures and staying away from dynamic SQL statements are two
techniques to reduce the attack surface of your application.
--Mary
On Sat, 11 Sep 2004 22:40:59 -0400, "rob" <rwc1960@.bellsouth.net>
wrote:

>There are 2 SQL databases involved and an MS Access program... also using
>windows authentication...
>The Ms Access program executes a stored procedure that resides in SQL
>database#1, the stored procedure updates data in SQL dataabase#2..
>Assume the user has rights to execute the stored procedure in database#1,
>how can you best limit the rights of the user to database#2, but still allow
>them to execute the stored procedure in database#1 that updates the data in
>database#2 ?
>
>