THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Roman Rehak

Granting EXECUTE on all stored procedures

I came across this a few months ago when I was researching security in 2005 and now thought it would be useful to post it here. I have saved code that loops through all stored procedures in the database and grants execute privileges to a database user. I've had it around since SQL Server 7.0. Now I discovered that in SQL Server 2005 you can create a server role and grant it EXECUTE privileges on all stored procedures. This solution is much more elegant, plus I don't have to rerun my script if I add more procs:

-- create custom database role
CREATE ROLE db_executor

-- grant EXECUTE permission

GRANT EXECUTE TO db_executor

 

-- add security account to the role

exec sp_addrolemember 'db_executor', 'YourSecurityAccount'

Note - security account can be a database user, database role, a Windows login or Windows group.

Published Wednesday, January 16, 2008 6:39 PM by roman
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

Another option is to use schemas.  For example, if all of your stored procedures are in dbo, it's a single call:

GRANT EXECUTE ON dbo TO [someone];

January 16, 2008 6:05 PM
 

ALZDBA said:

Just a single sidenote : the grant execute on schema also grants execution rights for the schema's functions !

January 18, 2008 11:56 AM
 

Alex said:

Adam, the full example would be:

GRANT EXECUTE ON SCHEMA::[dbo] TO [someone];

September 25, 2011 9:14 PM
 

Richard C said:

You can also grant execute to a windows group

grant execute to [Domain\ID]

May 11, 2012 2:42 PM
 

Prawin said:

Thanks for the Script.

September 7, 2012 11:04 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement