msdb.dbo.sp_help_job current_execution_step always returns 0 (unknown)

In SQL Server 2005, unless you belong to the sysadmin server role, executing
msdb.dbo.sp_help_job will always return "0 (unknown)" in the
current_execution_step column of first dataset returned.
Granting the user membership to the SQLAgentOperatorRole,
SQLAgentReaderRole, and SQLAgentUserRole does not make a difference.
Is this fixed in Service Pack 1?
Hello,
Based on my test, this issue is by design and still in SQL Server 2005 SP1.
According to the Books online, only the sysadmin could view the current
step informaton of a job.
sp_help_job (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms186722.aspx
SQL Server Agent Fixed Database Roles
http://msdn2.microsoft.com/en-us/library/ms188283.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti.../default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||I appreciate the response. I've read through those BOL entries before, so
maybe I'm missing something, or I haven't had enough coffee yet, but I can't
seem to find a note indicating that only users with the sysadmin role could
view the current job step.
In the event that this is indeed by design, are there any workarounds that I
can use to retrieve the step information without the user being added to the
sysadmin role?
|||Hello,
I did a deep research on how msdb.dbo.sp_help_job works.
Finnally, I found that msdb.dbo.sp_help_job invoke the system extended
stored procedure master.dbo.xp_sqlagent_enum_jobs and pass the parameter
@.is_sysadmin.
So my conclusion is unless you add the user to the sysadmin role, you could
not retrieve the step information for the job.
You may try to dig into the system stored procedure sp_help_job to find
whether there are any helpful information for you.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.