Excellent article and discussion, what would I do without sqlservercentral?

It occurred to me that with SQL 2005 you could combine it with a ddl trigger and eliminate the need for scheduling and the sql agent altogether:

create trigger tdGrantExecute

on database for

create_procedure, create_function

as

begin

 declare @exe varchar(128)

 declare @sql varchar(1000)

 declare cExe cursor forward_only for

  select objects.name

  from sys.objects

  inner join sys.schemas on objects.schema_id = schemas.schema_id

  where schemas.name = 'dbo'

   and type in ('P', 'FN', 'FS','AF','PC')

 open cExe

 fetch next from cExe into @exe

 while @@fetch_status = 0

 begin

  set @sql = 'GRANT EXECUTE ON dbo.[' + @exe + '] TO db_executor'

  exec (@sql)

  fetch next from cExe into @exe

 end

 close cExe

 deallocate cExe

/*

** SQL 2005 Object Types **

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

PK = PRIMARY KEY constraint

P = SQL stored Procedure

PC = Assembly (CLR) stored Procedure

FN = SQL scalar function

FS = Assembly (CLR) scalar function

FT = Assembly (CLR) table-valued function

R = Rule (old-style, stand-alone)

RF = Replication-filter-Procedure

SN = Synonym

SQ = Service queue

TA = Assembly (CLR) DML trigger

TR = SQL DML trigger

IF = SQL inlined table-valued function

TF = SQL table-valued-function

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored Procedure

IT = Internal table

*/

end

Note that in my environment I've restricted access to only the dbo schema.

Thanks again!