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.net...
> 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 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 ?
>
>

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