There are a few ways you could grant a user execution permission on stored procedures, through assigning permissions on different object hierarchies (objects/schema/database) you can control the level of permissions to ensure optimum security and flexibility.

This post will go through how to grant SQL Server execution permissions on individual stored procedure objects within a database, how to grant execution permission on all object within a schema (including functions), and finally how to grant execution permission across the whole database.

We will be using the SQL Server GRANT statement in order to assign execution permission to either a role or a user, the GRANT statement in its simplest form looks like this:

GRANT <permission> ON <object> TO <role or user>

Grant Execution on Stored Procedure Object to Role or User

This is generally the finest level of control, you can individually assign execution permissions to each stored procedure as and when required, the format of this statement is:

replacing <database>, <schema>, <stored procedure> and <user or role> with the relevant values for your environment.

Grant Execution on All Stored Procedures in Schema to Role or User

The nice thing about this option is that the user or role will implicitly get execution rights on any new stored procedure that gets added to the schema, which could remove the burden of individually assigning execution rights on stored procedure objects. It is important to note that this statement will also give execution rights to any object that gets “executed” in the database, so stored procedures, table-valued functions, scalar-valued functions, etc.

Grant Execution on All Stored Procedures in Database to Role or User

This gives most lax rights over executing stored procedures in the database in question, giving the user or role the right to execute any stored procedure within the SQL Server database.

This will obviously need to be run within a connection context of the relevant database.

One thing I (and many other DBAs as I’ve noticed) tend to do is have a db_executor role on any database that required it, this role will have execution permission across the database, and any user needed such permissions will be added to that role.