Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-03 : 09:57:59
|
| Eric H. writes "Hi,I'm a SQLServer DBA in a midsize financial firm and am stomped at this problem. We have an application that will be quering against 2 different databases(Database A, and Database B). Our main database is Database A, and it is the one where all our store procedure and users resided in. We are running into a big issue, apparently if a user don't have select rights in a table in Database B, he/she can't seem to be able to execute a store procedure in Database A to do a simple select against a table in Database B. We do not allow users to have any sort of SELECT rights in any of our production databases, i'm sure this is how it is with most production environment.For example, we created a store procedure in database A.The store procedure will be created by a SA or DBO.Create proc sp_testasselect firstname, lastname from B..EmployeesThen we grant execute rights to User 'Joe' to have execute rights on this store procedure. I make sure that 'Joe' is a user in Database B with Public role assigned.User Joe logs in and executes store procedure 'sp_test' in database A. He gets an error: "Select permission denied on object employees in database B". Why is that? The store procedure is created by a DBO.In normal circumstances, If you were to create the same exact store procedure going against database A instead, everything runs fine.Create Proc sp_testasselect firstname, lastname from A..OrdersThe user Joe has NO select rights on orders table in database A, and executes this store procedure in database A, he/she will get a result back. The store procedure is run fine. Amazing...Is there any way around this problem? What's the point of having distributed databases if managing rights can be so much trouble? I was told about application roles, but turning on application role within an application can remove all underlying rights within a user. This is a big issue, is there a better solution? Any help would be greatly appreciated!Thank You!Eric" |
|
|
Jay99
468 Posts |
Posted - 2002-04-03 : 10:06:54
|
| Take a look at 'Ownership Chains' in Books On Line. Typically you grant exec on stored procs without having to grant permissions on base tables . . .Jay<O> |
 |
|
|
|
|
|
|
|
Accessing data across databases