There are a few ways you could grant a user execution permission on stored procedures, through assigning permissions on different object hierarchies (objects/schema/database) you can control the level of permissions to ensure optimum security and flexibility.
This post will go through how to grant SQL Server execution permissions on individual stored procedure objects within a database, how to grant execution permission on all object within a schema (including functions), and finally how to grant execution permission across the whole database.
replacing <database>, <schema>, <stored procedure> and <user or role> with the relevant values for your environment.
The nice thing about this option is that the user or role will implicitly get execution rights on any new stored procedure that gets added to the schema, which could remove the burden of individually assigning execution rights on stored procedure objects. It is important to note that this statement will also give execution rights to any object that gets "executed" in the database, so stored procedures, table-valued functions, scalar-valued functions, etc.
This gives most lax rights over executing stored procedures in the database in question, giving the user or role the right to execute any stored procedure within the SQL Server database.
This will obviously need to be run within a connection context of the relevant database.
One thing I (and many other DBAs as I've noticed) tend to do is have a db_executor role on any database that required it, this role will have execution permission across the database, and any user needed such permissions will be added to that role.
I am facing a problem and don't know what the problem is (or if there is a problem). About a week ago a user requested that I create a Stored Procedure for a utility that extracts and update data from Excel. I granted EXECUTE permissions on the Stored Procedure and all worked since last week till this morning when the User received a permissions error. When I troubleshoot the issue I found out that the EXECUTE permissions are gone. What could be taking off these rights?
Is this consistently happening? As in, when u reinstate the execute permission, does it disappear again?