SQL Server Grant Execute Permissions on Stored Procedures

09 Dec
December 9, 2012

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:

GRANT EXECUTE ON <database>.<schema>.<stored procedure> TO <USER OR role>

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 EXECUTE ON SCHEMA::<schema> TO <USER OR role>

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.

GRANT EXECUTE TO <USER OR role>

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.

* * * *   1 vote
Tags: , , , , ,
3 replies
  1. Tagarika Magadzire says:

    I am facing a problem and don’t know what the problem is (or if there is a problem). About a week ago a user requested that I create a Stored Procedure for a utility that extracts and update data from Excel. I granted EXECUTE permissions on the Stored Procedure and all worked since last week till this morning when the User received a permissions error. When I troubleshoot the issue I found out that the EXECUTE permissions are gone. What could be taking off these rights?

    Reply
    • Links Naji says:

      Hmm, strange behaviour indeed!

      Is this consistently happening? As in, when u reinstate the execute permission, does it disappear again?

      Reply
      • Tagarika Magadzire says:

        Hi Links, I created and granted the rights to the Stored only a few weeks back and this is the first time this has happened. However, on one of our other in-house Timesheets system we’ve sporadically had entries that appear saved disappear say the following morning, and the users make printout every time they click save(and those entries appear as saved). This happens like once in a blue moon and now I’m just thinking out loud that could this be the same issue

        Reply

Trackbacks & Pingbacks

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>