Thursday, 15 December 2011

Execute Permissions

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.

No comments:

Post a Comment

Featured post

SQL Server 2008 and SQL Server 2008 R2 - OUT of SUPPORT July 2019

Both SQL Server 2008 and SQL Server 2008 R2 go out of extended support with Microsoft in July 2019. That’s fast approaching and as we enter ...