Let’s get this blog thing started! 
I sometimes find that appropriate permissions are not always assigned to stored procedures during creation. This usually occurs when a proc is scripted and re-deployed in a hurry, or when a non-DBA decides to play in my sandbox. I use this rather simple script to quickly find and grant all missing permissions.
Declare @schema_owner varchar(20); Set @schema_owner = 'dbo'; Select [name] As 'storedProcedure', 'Grant Execute On ' + @schema_owner + '.' + [name] + ' To [insertDatabaseRoleHere];' As 'sqlCode' From sys.objects With (NoLock) Where [name] Not In ( Select o.name From sys.database_permissions p With (NoLock) Inner Join sys.objects o With (NoLock) On p.major_id = o.object_id Inner Join sys.database_principals u With (NoLock) On u.principal_id = p.grantee_principal_id Where u.name = '[insertDatabaseRoleHere]' ) And [type] = 'P';
This is especially useful for when you have a common database role that all stored procedures need to belong to; for example, infrastructures that share role membership to allow applications to execute procs.
