I recently had call to set execute permissions on a database with over 200 procedures belonging to over 35 schemas (don’t ask .. its not my design).
As all of the schemas shared a common prefix (af2) it was relatively straight forward to write a query to give each of these procedures execute permissions to a particular role. This may not be the most elegant solution (I’m not saying it is), but it was a one off and it ran in a few seconds so I’m happy with it. It may be of use to others (in its original or altered form).
declare @schema nvarchar(max) declare @role nvarchar(max) declare @proc nvarchar(max) declare @sql nvarchar(max) set @role = 'someRole' declare curSchemas cursor for select [name] from sys.schemas where [name] like 'af2%' open curSchemas fetch next from curSchemas into @schema while @@fetch_status = 0 begin declare curProcs cursor for select p.name from sys.procedures p inner join sys.schemas s on p.schema_id = s.schema_id where s.name = @schema open curProcs fetch next from curProcs into @proc while @@fetch_status = 0 begin set @sql = 'grant execute on ' + @schema + '.' + @proc + ' to ' + @role exec sp_executesql @sql fetch next from curProcs into @proc end close curProcs deallocate curProcs fetch next from curSchemas into @schema end close curSchemas deallocate curSchemas