audit table update change RRS feed

  • Question

  • i have a table X  with the column   X1

    i need to  audit the change on the column   X1  and get the folowing details :

    DB_NAME    <<<<  the one change the data   .

    objectID    <<<<  the one change the data ( maybe  SP  or  job  )  .

    objectName .

    sourceHostname  .


    Monday, March 25, 2013 7:59 AM


  • Which version and edition of SQL Server do you have?

    audittime = sysdatetime() (or getdate()

    loginname = SYSTEM_USER / original_login(). (If they are different log both.)

    spid = @@spid

    sqltext = Now it becomes difficult, see below.

    DB_NAME = What exactly do you mean here? Do you cross-database commands, or what are you looking for.

    objectID = @@procid, but note that if you write a trigger to do the auditing, @@procid will be the object ID from the trigger. This only works if you have a table default or call an auditing routine from the stored procedures.

    objectName = object_name(@@procid), save caveates as above.

    sourceHostName = host_name(). Note that this is set in the connection string and can be spoofed.

    The only way to collect the SQL statement is SQL Server Audit which requires SQL 2008 or later and Enterprise Edition. In SQL 2012, some pieces of Audit is also available in Standard Edition.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Tuesday, March 26, 2013 3:01 AM
    • Marked as answer by Fanny Liu Monday, April 1, 2013 10:20 AM
    Monday, March 25, 2013 10:38 PM