MSBI Tutor

/*============================================= Variables: @MinHistExecutions - Minimum number of job step executions we want to consider @MinAvgSecsDuration - Threshold for minimum job step duration we care to monitor @HistoryStartDate - Start date for historical average @HistoryEndDate - End date for historical average Returns: One result set containing a list of job steps that are currently running and are running longer than two standard deviations away from their historical average. The "Min Threshold" column represents the average plus two standard deviations. note [1] - comment this line and note [2] line if you want to report on all history for job steps note [2] - comment just this line is you want to report on running and non-running job steps =============================================*/ DECLARE    @HistoryStartDate  datetime    , @HistoryEndDate  datetime    , @MinHistExecutions  int      , @MinAvgSecsDuration  int SET  @HistoryStartDate 

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