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 |
|
nodje
Starting Member
6 Posts |
 Posted - 2004-04-29 : 05:24:26
|
| Hi evceryone,i've got a Login 'mylogin' without server role.i've got a user 'myuser' related to 'mylogin' and which is db_owner of 'mydb'I use 'myuser' to connect 'mydb for my webapp. the latter can read/write data without pbs.But when it comes to executing SPs 'myuser' is not authorize.So I explicitely gave the permission to 'myuser' to execute the SPs.Still, it doesn't work. Why? I don't undertsand what I shall do to authorize SPs execution to this user.thanks for any tips.Jean |
|
|
vijayakumar_svk
Yak Posting Veteran
50 Posts |
Posted - 2004-04-29 : 06:22:09
|
| Check it up the sps are created from which user.Always create the sp from dbo user.eg create proc [dbo].samp....otherwise you have to specify the sp owner name as prefix of the spname.Work smarter not harder take control of your life be a super achiever |
 |
|
|
nodje
Starting Member
6 Posts |
Posted - 2004-04-29 : 11:49:15
|
| By default all SPs are created with the dbo prefix. And it sounds good to me. I don't want a SP to be specificaly tied to a particular user.Does it means that only the dbo user itself can execute them? I can hardly believe it! Someone else has to be able to execute it,And then what's the use of being able to set the EXEC permission to a particular user?? It doesn't sounds to help at all.Any way to execute a dbo.sp from a user that has a fifferent dbo right??regards,jean |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-29 : 14:22:29
|
| When you grant EXEC, the user can execute it regardless of who is the owner. The difference is that if an object is dbo.object, the user can just say EXEC proc. Otherwise, they have to say EXEC owner.proc.This even gets more complicated when it gets to the table level and you have multiple people owning tables (yuck). Just stick to dbo as the owner and you'll be fine.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
Security Problem