Written by Ian Treasure

Gethyn posted on the use of execute permissions a while ago (db_executor at http://www.gethynellis.com/2010/04/dbexecutor-role.html). Briefly, this approach uses a role – db_Executor – to which users are given membership. The execute permission is granted to the role.
 
I am using this approach to control access to stored procedures. Some developers started to complain that they could not execute their stored procedures. The first thing that I did was to check if their user was a member of the db_Executor role. It was, which was a surprise. I then started to check that the role had executor permissions. This was not so easy to do.
 
After a little digging, I used sp_helpprotect as follows.
 
In SQL Server Management studio, I ran:
 

sp_helprotect 'execute'

 
This returns the following:
 
Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 291
There are no matching rows on which to report.
 
OK – now I know that the problem is because the database role does not have execute permissions. So all I need to do is run:
 

GRANT EXECUTE TO [db_executor]

 
And if I repeat sp_helpprotect, I now see the following:
 
db_executor      dbo      Grant                Execute            .
 
which shows that db_Executor has execute permissions.