GRANT EXECUTE to all stored procedures

Question

SQL Server 2008 and Above:

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

For just a user (not a role):

USE [DBName]
GO
GRANT EXECUTE TO [user]

SQL Server 2005 introduced the ability to grant database execute permissions to a database principle, as you've described:

GRANT EXECUTE TO [MyDomain\MyUser]

That will grant permission at the database scope, which implicitly includes all stored procedures in all schemas. This means that you don't have to explicitly grant permissions per stored procedure.

You can also restrict by granting schema execute permissions if you want to be more granular:

GRANT EXECUTE ON SCHEMA ::dbo TO [MyDomain\MyUser]

In addition to the answers above, I'd like to add:

---

You might want to grant this to a role instead, and then assign the role to the user(s). Suppose you have created a role myAppRights via

CREATE ROLE [myAppRights] 

then you can give execute rights via

GRANT EXECUTE TO [myAppRights] 

to that role.

---

Or, if you want to do it on schema level:

GRANT EXECUTE ON SCHEMA ::dbo TO [myAppRights]

also works (in this example, the role myAppRights will have execute rights on all elements of schema dbo afterwards).

This way, you only have to do it once and can assign/revoke all related application rights easily to/from a user if you need to change that later on - especially useful if you want to create more complex access profiles.

Note: If you grant a role to a schema, that affects also elements you will have created later - this might be beneficial or not depending on the design you intended, so keep that in mind.