The EXECUTE permission was denied on the object 'Procedure Name', database ‘Database name’, schema 'Schema name'

This issue can be resolved by adding a Grant permission to the end of the new stored procedure

However, in real life scenario, where manual intervention is the cause of multiple errors; sometimes developers tend to miss out on this piece of code or most of the time they are not aware of it. The stored procedure executes perfectly well in a development environment (the developer has owner rights on the schema) but fails in QA or Production environment. Most of the time, this error is not realized until a major job fails, due to the absence of executor rights. 

declare @spname varchar(100)

declare @schemaname varchar(100)

declare @sql varchar(max)

Declare findSP Cursor         

For          

SELECT sys.objects.name, sys.schemas.name as [schema_name]

FROM    sys.objects 

INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id

WHERE type='P'        

Open findSP    

    

Fetch Next from findSP into @spname,@schemaname         

While @@FETCH_STATUS = 0         

begin

set @sql = 'GRANT EXECUTE ON ['+@schemaname+'].['+@spname+'] TO [sp_executor]'

exec (@sql)

fetch Next from findSP into @spname,@schemaname         

End         

Close findSP         

Deallocate findSP