locked
SQL Table updation tracking RRS feed

  • Question

  • Dear experts,

    I have SQL server 2008 R2 enterprise. I need to track which user is doing DML or DDL operation. I know that CDC if enabled will not track which user has done the modification. Is there any other way to achieve this. I think I will not go for After Trigger, need some other method.

    Kindly suggest.

    Thanks! 


    Uttam Kumar Sahoo
    Tuesday, December 13, 2011 7:22 PM

Answers

All replies

  • I have SQL server 2008 R2 enterprise. I need to track which user is doing

    DML or DDL operation. I know that CDC if enabled will not track which user
    has done the modification. Is there any other way to achieve this. I think I
    will not go for After Trigger, need some other method.

    Have you looked at SQL Server Audit?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 13, 2011 10:44 PM
  • Hi Uttam,

    For DDL operation, you can get a report by following steps:

    Right click your database—Reports—Standard Reports—Schema Changes History

    In this report you will see the changes made in the schema of the objects by DDL operations, includes the DDL operation, time, login name and user name.

    For DML operation, suggest add the OUTPUT clause to your DML statements, here is a reference: http://msdn.microsoft.com/en-us/library/ms177564.aspx


    Using SQL Server Audit can also get the login names.

    For more information, please see:
    http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx
    http://msdn.microsoft.com/en-us/library/cc280448.aspx


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Edited by Iric Wen Thursday, December 15, 2011 6:25 AM
    • Proposed as answer by Iric Wen Wednesday, December 28, 2011 1:46 AM
    • Marked as answer by Peja Tao Wednesday, December 28, 2011 7:19 AM
    Thursday, December 15, 2011 6:25 AM