Auditing CmdExec Job Steps..? RRS feed

  • Question

  • I have been working with a customer to help them implement a departmental SQL Server that conforms to their Corporation's security and auditing requirements for SQL Servers that are not under their control.  Suprisingly, we have been able to implement everything required, except for one very odd thing:

    "Send a notification whenever a CmdExec job is scheduled."

    So here's the bullet list of the situation:

      • SQL Server 2008 R2
      • C2 Auditing is already implemented
      • The c2 traces, along with contents of sys.connections and sys.sessions are remotely extracted and archived every 2 minutes from another (secure) SQL server.
      • I have implemented other "Notifications" as DBmail sent based on analysis of the most recent extracted data (from #3 above) every 2 minutes.
      • Yes, I know that "CmdExec" is a Step attribute, not a Job attribute, I am assuming that they mean any Job with a CmdExec Step in it.
      • And Yes, "scheduled" is ambiguous.  I think that I could go with either "a schedule for the Job was created or enabled" or "the Job started executing", since they are not sure what they meant either (don't ask, there's no help there).

    Now my problem is that I cannot figure out a reasonable way to do this.  As far as I can tell, there's no SQL Trace events in C2 (or out of it for that matter) that pertain to the SQL Agent system, and no other reasonable way to figure this out is apparent to me.

    At this point I am open to suggestions, though I need to try to keep it easy to implement within the sturture that we already have.

    Any Ideas?

    Thanks, ...

    -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
    Proactive Performance Solutions, Inc.
    "Performance is our middle name."

    Please! Remember to Vote all helpful replies as Helpful

    • Edited by RBarryYoung Wednesday, September 19, 2012 4:30 PM
    Wednesday, September 19, 2012 4:29 PM


All replies