3

I've used for years this query to monitor the execution of a job:

    SELECT j.name , ja.start_execution_date ,
   Datediff(ss, ja.start_execution_date, Getdate()) AS 'HasRun(seconds)'    
    FROM   msdb.dbo.sysjobactivity ja   
    INNER JOIN msdb.dbo.sysjobs J   
    ON j.job_id = ja.job_id 
    WHERE  ja.session_id = (select MAX(sac.session_id) 
                              from msdb.dbo.sysjobactivity sac 
                             where sac.job_id = j.job_id)
    AND ja.start_execution_date IS not NULL
    and ja.stop_execution_date is null

This solution runs beautifully on a normal MSSQL server. But now I’m using RDS from Amazon, and the security is too limited. I’ve not been able to even execute 

select * FROM msdb.dbo.sysjobactivity

with a login with the “most complete security profile available”.

I've tried other solutions posted on different blogs (like .... 

SELECT name, current_execution_status, job_id
INTO #Jobs
FROM OPENROWSET(‘SQLNCLI’, ‘server=(local);trusted_connection=yes’,

‘set fmtonly off exec msdb.dbo.sp_help_job’)

...)

But none of those work on RDS, due to lack of permissions, either on msdb tables, sps, or other elements.

Do you have a way to retrieve on a table/variable the status of a job (i.e. if it is running or not)) on RDS for a mortal user?

Laura Vargas
  • 31
  • 2

1 Answers1

0

Suppose there is a login with only public server role in SQL server RDS instance.

1) You need to add that login as user to msdb database USE [msdb] GO CREATE USER [your_username] FOR LOGIN [your_username]

2) after to add the user to role SQLAgentUserRole

EXEC sp_addrolemember [SQLAgentUserRole], [your_username];

3) In the end you should be able to run exec msdb.dbo.sp_help_jobhistory

Details about procedure can be found here

EDIT: You need to run queries, not to use GUI EDIT 2: RDS allows to see history of the job only if an user who runs sp is owner of the job

mija
  • 71
  • 3