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 |
|
timmoser
Starting Member
8 Posts |
 Posted - 2004-11-05 : 20:41:06
|
We are trying to have one user id for all of our applications that has execute permission on SP’s but none on tables. This seems to work for everything except where we are referencing tables in multiple databases.For instance:If I have a table “Golfers” in a database “Golfing” and another table called “Users” in a database called “Employees”. I can create an SP relating the Golfers table to the User table and it will work as long as I am logged in as admin. But if I use a SQL user that only has execute access to the SP it doesn’t work because the user doesn’t have select permission on the table in the other database. The select statement would look like this:SELECT a.*, b.* FROM Golfing.Golfers a, Employees.UsersWHERE a.golferId = b.userid I don’t want to give the user select access to tables directly, I prefer to just give the user access to the SP where necessary.Do I have to change something in the database? Thanks for your help. |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-06 : 01:59:52
|
| Turn Cross DB Ownership Chaining on?You can call an SProc in the OtherDatabase [for which the current user has Execute permission].Looks like in this case that SProc would have to return some values, which you store in a temporary table, and then join to the other tables. Not very elegant.Kristen |
 |
|
|
timmoser
Starting Member
8 Posts |
Posted - 2004-11-06 : 10:17:05
|
| In the real SP it does contain a similar "SELECT" statement. When the app calls the SP the results are assigned to a DataSet object (MS ADO.Net object). No need for temp tables here.Thanks for your help. |
|
|
|
|
|
|
Security - Stored Procedures