There are cases that we only want to grant read-write only access then also enable users to execute stored procedure with in the SQL Server database.
In order to do this we will need to:
- Create a new Role named, db_exec_storedprocedure
CREATE ROLE db_exec_storedprocedure
- Grant that role with execute stored procedure
GRANT EXECUTE TO db_exec_storedprocedure
- Then add the user as a member of that role.
That’s it you can now grant specific permission to execute stored procedure.