Post reply

tracking job deletion

  • April 13, 2011 at 11:38 am

    #234898

    we had two jobs disappearing from our productive BI SQL2K8 box and would like to find out who deleted them.

    Unfortunately with deleting the job, all related history data are gone as well and any reference to it are purged from MSDBs dbo.sysjobs table + of course, all references in other tables.

    Windows app evt yields no info on when or by whom the job deletion was triggered and neither do the SQL server or error logs. string searching for the job name in the SQL log directory turned up no results either

    Anybody any clue where to look ?

    thanks

    Boris B

  • April 13, 2011 at 1:11 pm

    #1311272

    In SQL 2008 You have now Auditing.

    Create a Database Specific Audit on [msdb] and track this.

    [Audit Action Type] = Execute

    [Object Name] = [dbo].[sp_delete_job]; [dbo].[sp_delete_jobstep]; [dbo].[sp_delete_jobsteplog]

    [Principal Name] = [public]

    This will catch the crazy dude who is trying to ruing your job.

    Here is a good article to get started with Database Auditing.

    QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809

  • April 14, 2011 at 3:51 am

    #1311491

    thanks !

  • April 14, 2011 at 4:41 am

    #1311495

    However, unless you had an audit running when the delete happened that won't help this time. It'll help for future occurrences.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 14, 2011 at 8:31 am

    #1311643

    it doesn´t work.

    I manage to create an audit on instance level & enable it, but when trying to select e.g. the object name [dbo].[sp_delete_job] in the audit specification on MSDB the GUI pretends to not find it. in the respective browser selection window not a single system sp is displayed

    tried with both an ADS account that is sa equivalent as well as sa proper

  • April 14, 2011 at 9:54 am

    #1311702

    ok - finally got it work

    problem was, it doesn´t work in the GUI on both 10.0.2757 and the 10.0.4000, but the script at least works on the 10.0.4000, allthough ";" is not accepted as delimiter. the complete script then is a bit long-winded. a bit of a shortcoming is that it returns @job_id and not @name in the app event log, but the important bits are of course session ID & user + timestamp.

    thanks so much again - owe you a beer !

    USE [msdb]

    GO

    CREATE DATABASE AUDIT SPECIFICATION [MSDB sp deletion]

    FOR SERVER AUDIT [delete sp]

    ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_job] BY [public])

    GO

    USE [msdb]

    GO

    CREATE DATABASE AUDIT SPECIFICATION [MSDB sp deletion]

    FOR SERVER AUDIT [delete sp]

    ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_jobstep] BY [public])

    GO

    USE [msdb]

    GO

    CREATE DATABASE AUDIT SPECIFICATION [MSDB sp deletion]

    FOR SERVER AUDIT [delete sp]

    ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_jobsteplog] BY [public])

    GO

Viewing 6 posts - 1 through 5 (of 5 total)

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