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:
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.