SQL Server Grant Execute Permissions on Stored Procedures
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.
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?
Hmm, strange behaviour indeed!
Is this consistently happening? As in, when u reinstate the execute permission, does it disappear again?
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