How to grant execute permissions to the stored procedures in a specific schema?

Question

Is there a way in SQL Server 2012 to grant execute all stored procedures in one schema? For additional info, these stored procedures are doing just a select.

Answer 1

Try something like that. It creates a new role and grants execute permission to a schema.

CREATE ROLE db_executor
GRANT EXECUTE ON SCHEMA::schema_name TO db_executor
exec sp_addrolemember 'db_executor', 'Username'

Replace schema_name with your schema and 'Username' with your user.