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 |
|
Naanan
Starting Member
3 Posts |
 Posted - 2008-03-19 : 16:20:53
|
| Hi,I have a Stored Procedure in one database that grabs data from another database. I don't want the user to be able to read data from the tables that the stored procedure Selects from but I would like the user to be able to run the stored procedure. Pretty standard request I think.What I have done is to give the user in question a login then assigned them the Execute Permissions on the stored procedure. Unfortunately they are still unable to run the stored procedure from my web app. I have "allow anonymous access" turned on but I am still getting an error when the user tries to execute the stored procedure.Am I missing something here or could there be a bigger issue?Thanks,Patrick |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-20 : 01:32:39
|
| When the tables are remote to the stored procedure, the user must have access to the table directly and not just execute on the sproc.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
PABluesMan
Starting Member
26 Posts |
Posted - 2008-03-25 : 15:28:58
|
| If you really want to lock it down, you could create a proc on the remote server that only grabs the data they need and grant exclusive permissions to that proc. You could then call it from your local proc.Kind of a kludge, but it's just crazy enough to work.I geek, therefore I am |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2008-03-25 : 15:40:35
|
| You could enable cross database ownership chaining. But, nobody on this forum is stupid enough to acutally recommend it. Me included. So, don't enable cross database ownership chaning. It is a major security risk. I would follow PaBluesMan's recommendation.- Eric |
|
|
|
CShaw
Yak Posting Veteran
65 Posts |
Posted - 2008-03-25 : 15:41:39
|
| Have you looked at cross database ownership chaining? I am not real strong on the subject, but I think that might get you what you want. I would research it in depth so you see the side effects are. Chris Shawwww.SQLonCall.com |
|
|
|
PABluesMan
Starting Member
26 Posts |
Posted - 2008-03-25 : 17:01:47
|
| Either that was horrible timing or an example of subtle humor ... :)I geek, therefore I am |
|
|
|
|
|
|
Stored Procedure Security