Hi All: I've recently been looking at tightening security on my sql-backended webs. One of the things I started rolling back were permissions on the sql account used in web.config to connect to the database. Where in the past I just automatically assigned the sql login "dbo" role, I've started restricting the login to "db_datareader" and "db_datawriter" roles, in the hope that this would prevent a hacker from using sql injection to return object names and the like.

I just ran into a problem, however, with a database that uses a lot of SProcs...if the sql login only has datareader/datawriter privs, executing the SProc in code-behind throws an error:

The EXECUTE permission was denied on the object 'mySP', database 'myDB', schema 'dbo'.

I found on MSDN the instructions to modify the SProcs with "With Execute As Owner"...I updated the procedures with that line, confirmed it was there, but I'm still getting the error message above...where am I going wrong?

you need to give execute permission to your stored procedures and functions

GRANT EXECUTE  ON <object> TO <user>

Ganeshyb:

you need to give execute permission to your stored procedures and functions

GRANT EXECUTE  ON <object> TO <user>

I thought I did do that, by adding "with execute as dbo" to the SProc...but that did not work. Is there a different way to do it? Where does one grant execute privileges from?

dotnetnoob:

Ganeshyb:

you need to give execute permission to your stored procedures and functions

GRANT EXECUTE  ON <object> TO <user>

I thought I did do that, by adding "with execute as dbo" to the SProc...but that did not work. Is there a different way to do it? Where does one grant execute privileges from?

I don't think they are the same. With "With Execute as dbo" clause you makes the stored procedure to be  run under the context of the dbo, however, the problem now is that you cannot execute this stroed procedure (you don't have sufficient permission). Grant Execute on and With Execute as talk about different things i think, the forms assign permissions to the SP, while the latter assign permission to the user. Try to run Grant Execute on <your stored proceude> to <your login in user> and see if it helps.

Hope my suggestion helps

Bo Chen – MSFT:

dotnetnoob:

Ganeshyb:

you need to give execute permission to your stored procedures and functions

GRANT EXECUTE  ON <object> TO <user>

I thought I did do that, by adding "with execute as dbo" to the SProc...but that did not work. Is there a different way to do it? Where does one grant execute privileges from?

I don't think they are the same. With "With Execute as dbo" clause you makes the stored procedure to be  run under the context of the dbo, however, the problem now is that you cannot execute this stroed procedure (you don't have sufficient permission). Grant Execute on and With Execute as talk about different things i think, the forms assign permissions to the SP, while the latter assign permission to the user. Try to run Grant Execute on <your stored proceude> to <your login in user> and see if it helps.

Hope my suggestion helps

After googling some more, it looks like the best way to handle it is a script that iterates through all the SPs and assigns execute as?

select 'grant execute on '+s.name+'.'+o.name+' to RoleX'+char(10)+'go'
from sys.schemas s
join sys.objects o
on s.schema_id = o.schema_id
where type = 'P' order by s.name+'.'+o.name;