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.
Cross-posted from SQLBlog! -
http://www.sqlblog.com