Grant execute any procedure in a database:

hi, 

data reader, datawriter database roles  can read data from tables , write to tables , but cannot view the stored procedure

DDL admin database role : can create table , drop a table but  cannot execute stored procedure

so 

--to execute sp:

 

To grant permissions for a user or role to execute any procedure in a database:

USE MyDatabase

GRANT EXECUTE TO UserOrRoleName;

 

To grant permissions for a user or role to execute any procedure in any database:

EXEC sp_msforeachdb 'USE ?; GRANT EXECUTE TO UserOrRoleName'

to execute a stored procedure a user must be granted execute permissions

Thanks