SQL Server db_executor Role

OK, so SQL Server still doesn't have a db_executor role.

Here's a way to add one to any given database:

 
if not exists (select * from sys.database_principals where name='db_executor' and type='R')
 create role db_executor

declare @name sysname
declare @sql nvarchar(250)

declare cur cursor for
 select name
 from sys.procedures
 order by name

open cur
fetch next from cur into @name
while @@FETCH_STATUS = 0 begin
 set @sql = N'grant execute on OBJECT::'+@name+N' to db_executor'
 print @sql
 exec sp_executesql @sql
 fetch next from cur into @name
end

close cur
deallocate cur

You'll need to re-run this every time you create a new stored procedure. You could expand on this to loop through all non-Master databases, or to use the DDL trigger feature of SQL Server 2005+, like this:

 
create trigger add_executor_role
on database
for create_procedure
as begin
 [sql from above goes here]
end

(For more on DDL triggers, see Using DDL Triggers in SQL Server 2005 to Capture Schema Changes)