THE SQL Server Blog Spot on the Web

Welcome to - 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



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

Privacy Statement