Who created a new job on my server

Question

Post reply

Who created a new job on my server

SUBRAHMANYA HEDGE

SSCommitted

Points: 1514
More actions
August 13, 2009 at 8:39 am

#137259

Hi Friends,
On a Dev server, someone has created a new SQL job and changed the owner to sa. Is there a way I can find who created this job?
I checked in error log & event viewer for the job creation time, but couldn't find anything.
-Subbu

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

Try the default trace, but I'm not sure if will be there.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I did a quick test in the Default Trace and I didn't see anything for job creation.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 3

Here is what you can do and you can always mail me for assistance during this time (hi_abhay78@yahoo.co.in).

First create or modify a job and run profiler at the background (filter on SPID) .

Then find out which object , SP is changed or altered .

For sure sys.jobs , sys.jobhistory and similar tables will be there or some SPs will be there .

Capture those names and then we can decide what all event classes we need to use to create the WMI alerts on it .

Once done it will capture the event in the table with the help of a job and an alert.

Regards

Abhay

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

hi_abhay78 (8/14/2009)
Here is what you can do and you can always mail me for assistance during this time (hi_abhay78@yahoo.co.in).
First create or modify a job and run profiler at the background (filter on SPID) .
Then find out which object , SP is changed or altered .
For sure sys.jobs , sys.jobhistory and similar tables will be there or some SPs will be there .
Capture those names and then we can decide what all event classes we need to use to create the WMI alerts on it .
Once done it will capture the event in the table with the help of a job and an alert.
Regards
Abhay

Interesting that many of your recent answers to problems have been to use WMI alerts. I'd be interested in seeing how you would use WMI in these areas. I have several tasks I know would benefit from using WMI, but I haven't found any good tutorials that would help me learn more about the subjust, and seeing some working code would be helpful.

SUBRAHMANYA HEDGE SSCommitted Points: 1514 More actions · Answer 5

Steve Jones - Editor (8/13/2009)
Try the default trace, but I'm not sure if will be there.

Hi Steve,

I checked in default trace. But in default trace mainly we'll have 'Object:Created' & 'Object:Deleted' event classes. And as job is not a seperate object, its creation or deletion will not be captured by default trace. I think to find out job creation details, we should have TSQL events in the trace.

Thanks

Subbu

SUBRAHMANYA HEDGE SSCommitted Points: 1514 More actions · Answer 6

hi_abhay78 (8/14/2009)
Here is what you can do and you can always mail me for assistance during this time (hi_abhay78@yahoo.co.in).
First create or modify a job and run profiler at the background (filter on SPID) .
Then find out which object , SP is changed or altered .
For sure sys.jobs , sys.jobhistory and similar tables will be there or some SPs will be there .
Capture those names and then we can decide what all event classes we need to use to create the WMI alerts on it .
Once done it will capture the event in the table with the help of a job and an alert.
Regards
Abhay

Hi Abhay,

Thanks for the reply. Also I can set WMI alerts or any other alert going forward. But already someone has created a job and made sa as job owner. I want to know who has created this job.

Thanks

Subbu

myheadhurts Ten Centuries Points: 1262 More actions · Answer 7

Delete/disable/munge it...see who hollers

-MarkO

"You do not really understand something until you can explain it to your grandmother" - Albert Einstein

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 8

Hi - if the job contains one or more schedules, you may find a clue by running this query:

SELECT

sp2.[name] job_owner

, sp.[name] scheduled_by

, j.[name] job_name

, ss.[name] schedule_name

FROM sysjobs j

INNER JOIN sysjobschedules s

on s.job_id = j.job_id

INNER join sysschedules ss

ON s.schedule_id = ss.schedule_id

INNER JOIN master.sys.server_principals sp

ON sp.sid = ss.owner_sid

INNER JOIN master.sys.server_principals sp2

ON j.owner_sid = sp2.sid

The query will show the job owner ('sa' in your case), but will also show the name of the person who set up the schedule(s) - which AFAIK isn't otherwise visible via SSMS, unlike the job owner who is visible on the job properties window

🙂

webrunner SSC-Dedicated Points: 31882 More actions · Answer 9

I think this is a fascinating and important thread - perhaps it can be made into an article.

I also wonder - can you guys help the user set up something so he can be notified when such object creations happen, going forward? If he's running SQL 2005 or later, aren't there DDL triggers or other tools to flag those actions? That way he would have a record of his own as soon as it happened, in case there is malicious activity and the attacker is trying to alter logged data.

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html