Granting execute rights to all stored procedures used to be a cumbersome problem up to before SQL Server 2005. You either had to give elevated rights to the user or run a script to GRANT EXECUTE on every stored procedure.

I still see plenty of databases where a DBA has granted db_owner rights, simply to allow stored procedures use.

There is now a more elegant solution, using ROLES. Which also have the added advantage of complying with security best practises, and many corporate security policies I’ve read.

-- create database role
CREATE ROLE db_execute
-- grant EXECUTE permission to database role db_execute
GRANT EXECUTE TO db_execute
-- add database user account to the role
exec sp_addrolemember 'db_execute', 'database_user_acct'

An alternative is to use schemas and apply execute on the schema

e.g

GRANT EXECUTE on myschema TO [myuseracct]

Be careful, in applying these roles – as you may allow access to functions or stored procedures, which are developed in the future. Adding this role to the database user will allow all future stored procedures to be accessed.

Republished from http://www.sqlserver-dba.com.