Post reply

tracking data changes

  • March 7, 2012 at 9:19 am

    #252055

    Hi,

    How can I tell which user inserted or updated data in a table? Is that possbile i can know?

    Thanks

  • March 7, 2012 at 10:13 am

    #1456128

    imranx101 (3/7/2012)

    Hi,

    How can I tell which user inserted or updated data in a table? Is that possible i can know?

    Thanks

    Not without having something in place first; SQL doesn't keep track of whodunnit information on DML(data changes)., but certainly has the ability to do it if you want it to.

    The default trace does keep track of who made DDL changes, but it only keeps track of the last 100 meg of changes on the entire server; so a server that a lot of DDL changes will see the oldest events overwritten for space reasons.

    even the log only keeps track of the data changes...not who did it.

    Creating a server side trace is probably the most recommended way to do that.

    If you were to enable CDC on some of your tables you want to track, that keeps track of data changes, but i'm not offhand sure if it keeps track of the user that did it with checking with google.

    yet another option is a column in your table with a default value if suser_name(), for example or a trigger that writes to an audit table you would create to track changes with.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 7, 2012 at 11:58 am

    #1456168

    Along with what lowell said, I would be cautious with how much logging I do, logging everything takes a large performance hit on the server and on the disk. Audit what you need and no more..

    CEWII

Viewing 3 posts - 1 through 2 (of 2 total)

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