Auditing changes to Database Maintenance plans

  • Aaron.Christenson.ctr

    SSChasing Mays

    Points: 605

    Is there a way to log changes made to database maintenance plans and / or SQL Agent Jobs? I have a maintenance plan that was changed and I am unable to determine when or by whom made the change. There are too many users that have the rights to make this change to ask. I understand that my first step should be limit the number of users that have System Administrative access.

    Thanks

    Aaron

    MCSE, MCSA, MCDBA

    Aaron
    MCITP: Database Administrator, MCITP: Server Administrator, MCTS: Windows Server 2008 Active Directory – Configuration, MCTS: Windows Server 2008 Network Infrastructure – Configuration, MCTS: Windows Server 2008 Application Platform – Configuration, MCTS: SQL Server 2005, MCTS: Microsoft Exchange Server 2007 – Configuration, MCSE 2003, MCSA 2003, MCDBA, Security+, CCNA
    https://www.mcpvirtualbusinesscard.com/VBCServer/AaronChristenson/interactivecard

  • SQL ORACLE

    One Orange Chip

    Points: 27807

    There is a column, date_modified in the msdb..sysjobs. It tells us when a job is modified. But we need to create another table to compare this one in order to find what is modified.

    If you would like to know who did it, you may have to set a profiler.

  • Aaron.Christenson.ctr

    SSChasing Mays

    Points: 605

    Thank you very much, that might be enough, then I can look at the secruity log in Windows to see who was logged in at the time.

    Aaron

    Aaron
    MCITP: Database Administrator, MCITP: Server Administrator, MCTS: Windows Server 2008 Active Directory – Configuration, MCTS: Windows Server 2008 Network Infrastructure – Configuration, MCTS: Windows Server 2008 Application Platform – Configuration, MCTS: SQL Server 2005, MCTS: Microsoft Exchange Server 2007 – Configuration, MCSE 2003, MCSA 2003, MCDBA, Security+, CCNA
    https://www.mcpvirtualbusinesscard.com/VBCServer/AaronChristenson/interactivecard

  • sizal0234

    SSCommitted

    Points: 1995

    maintenance plan was changed and because I see the differentail backup was for only user db's but it has been set to all db's which failed the job. I looked into the sysjobs table and date_modified shows only my change which was to fix ( I switched from all to user db's only for diff bak. Is it possible you can also edit or modify the rows or delete any row from sysjobs? what is the full proof way of tracking who made the change; what and was the change to remedy the failure quickly.

  • Michael L John

    One Orange Chip

    Points: 25717

    This is an 11 year old thread.  
    I suggest you create a new thread, in the proper forum.  You aren't really using SQL 7 or 2000, correct?

    And possibly re-phrase your question.  From reading it, it's not clear what the actual question is you may be asking.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • sizal0234

    SSCommitted

    Points: 1995

    I am sorry if it was not clear. Yes I can surely great new post. Happy new year!

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

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