Grant permisiions to a stored procedure

[Simonbegg]

Hello All
Is it possible to grant db_owner (or any other admin role) permissions to a stored procedure, so that that specific procedure can run with "enhanced" permissions by a std user?

Cheers
Simon

 

[TheBugSlayer]

No. Grant the necessary permissions to the user. SPs run under a security account and can only carry out actions which the account owner is allowed to carry.

 

[SQLSister]

If you grant Exec on a stored procedure, you are granting the rights to perform the actions in the AP to the user. THe exception is if you have used dynamic SQL (Which you should avoid if at all possible). Then you must grant the rights at the table/view level.

I haven't tried it, but I would also guess that if you have specifically denied the user or role permissions on a table, I believe that would take precedence over being given the execute permission on an SP which accesses that table.

I say this only because when permissions conflict, deny is generally given the higher precedence. I do know that if you have neither granted nor denied perssions to a table, the sp will execute if you grant Execute permissions.

Questions about posting. See faq183-874

 

[Simonbegg]

Thanks folks.