Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Output who invoked a SQL Job Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 6:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 10, 2014 6:24 AM
Points: 81, Visits: 243
Hi all,

Is there a way I could output the username of whoever invoked a SQL job each time it runs? ...and perhaps write it to a table?

I have used suser_name() to output the user that runs each step, but these are all being automatically started by the SQL Agent service account, so obviously that username is all I get.

Any ideas?

Thanks,

Matt
Post #1373211
Posted Wednesday, October 17, 2012 5:30 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:05 AM
Points: 943, Visits: 2,951
You can find this in the job history table.

SELECT  j.name,
jh.run_date,
jh.run_time,
SUBSTRING(jh.message, CHARINDEX('.', jh.message) + 3, CHARINDEX('.', jh.message, CHARINDEX('.', jh.message) + 1) - CHARINDEX('.', jh.message) - 3) AS MiddleMessage
FROM msdb..sysjobhistory jh
JOIN msdb..sysjobs j
ON jh.job_id = j.job_id
WHERE jh.step_id = 0





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1373736
Posted Wednesday, October 17, 2012 6:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 10, 2014 6:24 AM
Points: 81, Visits: 243
That looks good - many thanks!
Post #1373765
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse