See: http://www.sqldbatips.com/showarticle.asp?ID=8
In SQL Server 2000 we had to grand execute rights to all stored procedures by altering the create scripts or executing a sql query like:
declare @sql nvarchar(4000)
declare @db sysname ; set @db = DB_NAME()
declare @u sysname ; set @u = QUOTENAME(‘<insert_username>’)
set @sql =’select ”grant exec on ” + QUOTENAME(ROUTINE_SCHEMA) + ”.” +
QUOTENAME(ROUTINE_NAME) + ” TO ‘ + @u + ”’ FROM INFORMATION_SCHEMA.ROUTINES ‘ +
‘WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),”IsMSShipped”) = 0’
exec master.dbo.xp_execresultset @sql,@db
In SQL Server 2005 we can grant execute rights to a role instead of a stored procedure:
/* CREATE A NEW ROLE */ CREATE ROLE db_executor /* GRANT EXECUTE TO THE ROLE */ GRANT EXECUTE TO db_executor