To fetch SQL Agent JOBs Info.,

To fetch SQL JOBs' information from the current Instance

Use Master
Go
 

EXECUTE msdb.dbo.sp_sqlagent_refresh_job
Go

The EXECUTE permission was denied on the object 'xp_sqlagent_is_starting', database 'mssqlsystemresource', schema 'sys'

The following Error occurs when execute the following statement

Declare @retval Int
EXECUTE master.dbo.xp_sqlagent_is_starting @retval OUTPUT
Select @retval

Error:
Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 1
The EXECUTE permission was denied on the object 'xp_sqlagent_is_starting', database 'mssqlsystemresource', schema 'sys'.

It means, The current user doesn't have EXECUTE permission for the object 'xp_sqlagent_is_starting'.

To listout the permissions for the Object in current user/login:

Select * from fn_my_permissions('xp_sqlagent_is_starting','Object')
Go

It should return the following permissions(EXECUTE)







If not. Give the EXECUTE permission for the Object and for the User! (Login into SysAdmin login)
1. Login into SysAdmin role login (i.e: sa)

2. Execute the following statement to provide proper permission to execute the object.

Grant Execute On master.dbo.xp_sqlagent_is_starting to <YourLogin>
Go