GRANT EXECUTE to all stored procedures

Question

Does the following command effectively give the user, "MyUser," permission to execute ALL stored procedures in the database?

GRANT EXECUTE TO [MyDomain\MyUser]

Answer 1

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]

Answer 2

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]

Answer 3

GRANT EXECUTE TO PUBLIC

This one surely help

Answer 4

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.