184

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

GRANT EXECUTE TO [MyDomain\MyUser]
Matt
  • 25,467
  • 18
  • 120
  • 187
Chad
  • 23,658
  • 51
  • 191
  • 321

3 Answers3

314

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]
Antony Scott
  • 21,690
  • 12
  • 62
  • 94
  • 33
    +1 plus: it even grants EXECUTE permissions to future stored procedures, e.g. those that aren't in your database yet - but will be created later on. – marc_s Feb 17 '12 at 05:57
  • 3
    I think it's worth noting that your `user` may have to be within square brackets. This was true in my use case at least in part because my user had a domain attached (ie. it had a \ character in it). edit: fixed unescaped slash character – PrinceTyke Jul 22 '15 at 11:56
  • 1
    Why not assign the user to the db_ddladmin role? "Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database." - see [here](https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles) – Michael Tobisch Feb 08 '18 at 10:41
  • 1
    @MichaelTobisch here just need to execute Stored Procedures. DDL Role must be used in Create, Alter, Drop, ... scenarios. These links must be useful: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-2017 and https://www.geeksforgeeks.org/sql-ddl-dml-dcl-tcl-commands/ – QMaster Mar 02 '19 at 11:25
  • 4
    And the next level of adding a user to a role in case it saves someone another step of research. ALTER ROLE db_executor ADD MEMBER YourUserNameHere – Piwaf May 16 '19 at 19:52
  • 2
    @MichaelTobisch db_ddladmin don't give access to execute a stored procedure; it gives access to create/alter/delete the stored procedure. – Samuel Jul 26 '19 at 15:35
  • How come once you do this, if you go to the properties of a stored procedure, you don't see the role in there? I tried to do this with an AD group, and didn't see them. I then made a db role and added the AD group to the role, and then ran the grant execute on the role. That doesn't show in the stored procs' permissions properties either. Why not? – missscripty Jan 08 '21 at 16:01
80

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]
Robin Minto
  • 15,027
  • 4
  • 37
  • 40
20

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.

Matt
  • 25,467
  • 18
  • 120
  • 187