I have created a stored procedure and passed jobname to sp_stop_job. This stored procedure is invoked by a SSMS Job. Though the job is running for a long time, sp_stop_job is not stopping the job . But when the same statement (Exec sp_stop_job ) is executed outside of the stored procedure, it is stopping the stuck job.
It looks like a permission issue and please help with the persmissions to be granted to Stored proc/Job/UDF.
SET @Jobname = (SELECT JobName from UDF()
WHERE StatusFlag = 'Stuck'
DECLARE @dynSql NVARCHAR(MAX) = ''
SELECT @dynSql += N' Exec msdb.dbo.sp_stop_job ''' + @Jobname + N''''+ CHAR(10) + CHAR(13)