Post reply

Who created a new job on my server

  • 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

  • August 13, 2009 at 8:49 am

    #1038746

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

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 14, 2009 at 6:53 am

    #1039286

    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

  • August 14, 2009 at 9:15 am

    #1039446

    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)

  • August 14, 2009 at 9:40 am

    #1039474

    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.

  • August 16, 2009 at 10:48 pm

    #1040019

    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

  • August 16, 2009 at 11:00 pm

    #1040022

    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

  • August 17, 2009 at 11:37 am

    #1040377

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

    -MarkO

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

  • August 18, 2009 at 8:26 pm

    #1041246

    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

    🙂

  • August 19, 2009 at 8:10 am

    #1041556

    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

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

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