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 ?
Permissions on indirectly referenced objects are not needed as long as the
objects have the same owner (i.e. owner maps to the same login). In the
case of dbo-owned objects in different databases, the databases need to have
the same owner so that the dbo user maps to the same login. You can change
database owners using sp_changedbowner, if needed. Also, cross-database
ownership chaining is a configurable option in SQL 2000 SP3 and needs to be
enabled in both databases. You can enable this using sp_dboption:
EXEC sp_dboption 'Database1', 'db chaining', true
EXEC sp_dboption 'Database2', 'db chaining', true
The main security consideration with cross-database chaining is that you
should enable the option only if you trust users with object CREATE
permissions in those databases. See the SQL 2003 SP3 Books Online for more
info.
Hope this helps.
Dan Guzman
SQL Server MVP
"rob" <rwc1960@.bellsouth.net> wrote in message
news:MYW0d.113845$0o5.105823@.bignews1.bellsouth.ne t...
> 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 ?
>
>
>