Post reply

Track User level metadata changes in SQL Server

  • July 25, 2017 at 3:30 am

    #322786

    Scenario is one of db user(employee) resigns his job, i need to track the metadata changes done by that db user, any script please suggest
    Note: i have not enabled CDC

  • July 25, 2017 at 4:06 am

    #1952518

    You can look in the default trace. It rolls over though, so doesn't go back very far.
    Other than that, you need custom monitoring (not CDC, that's for data)

    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

  • July 25, 2017 at 5:03 am

    #1952524

    Any Script to do past 2 weeks monitoring, please suggest

    Thanks in advance

  • July 25, 2017 at 5:48 am

    #1952530

    Have a look at this and see whether it will do what you need.
    Obviously, this won't work for changes made before it is implemented.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • July 25, 2017 at 5:49 am

    #1952531

    GA_SQL - Tuesday, July 25, 2017 5:03 AM

    Any Script to do past 2 weeks monitoring, please suggest

    GilaMonster - Tuesday, July 25, 2017 4:06 AM

    You can look in the default trace. It rolls over though, so doesn't go back very far.

    Whether you have 2 weeks of data in the default trace depends on how active your system is. I've seen it hold 12 hours of data, I've seen it hold several months of data.
    If it's not in the default trace, then you will need to have had custom monitoring in place to track schema changes by user. SQL does not do this by default baring the default trace.

    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

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

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