How do I give a user access to execute Stored Procedures

[Maritime]

Hi this might be a newbie question, but any help would be appreciated. How do I give a user access to execute Stored Procedures? Or the Stored Proceedures that they create.

Thanks

 

[nigelrivett]

If a user creates an SP then they will be it's owner (may cause other problems) and so have execute permission.

To give execute permission see grant in bol

grant execute on sp to user

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Maritime]

I acutally found how to do it, by selecting the stored Procedure and from the properties tab setting the execute permission, however the user still need to be able to modify the SP. Any other ideas. The user of the SP is dbo and we do not want to make the user the owner of the SP because this can cause problems in the end, when a user leaves or need to be deleted.

Thanks

 

[nigelrivett]

To allow to update and execute dbo owned SPs you will need to add the user to the database ownet role.

If they can amend and execute dbo SPs then they can do almost anything dbo can so it's not much more of a security risk.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Corran007]

Another possibility would be something a friend of mine did. They had a similar situation. What they did is create an application that handles the security of mainting the stored procs and editing them. Basically they had all the stored procs and who could and couldnt make and edit them. Its extra work but it gave them the fine control on only being able to edit certain procs for certain users.