I have an Azure SQL database with this security setup:
db_datareaderroles. The user is used to execute database change scripts during application deployment.
db_datareaderroles. The user is used by the application to work with the data.
I wanted the app user to also execute a stored procedure, say
dbo.sp_MyProc. I did following:
grant execute to [SchemaUpdater] with grant optionusing an admin account, so that change scripts can grant permissions for any SPs.
- Created the stored procedure in a change script.
- Tried to
grant execute on [dbo].[sp_MyProc] to [App]in the same change script. That didn't work.
The step failed with
Cannot find the object 'sp_MyProc', because it does not exist or you do not have permission.
What's interesting, that if the
grant execute to [App] instead, with no individual SP mentioned, it works.
My questions are:
- Why can't
SchemaUpdatergrant a permission on a single SP, but can grant it on all SPs?
SchemaUpdatermissing some other permission, so that it could grant permissions to individual SPs?