none
SQL Server 2008 audit. Parameter values are not shown in Audit Log File viewer

    Question

  • Hello,

    I'm trying to use SQL Server 2008 auditing. In audit Log viewer in SQL Server Management Studio I can see details for every auditing event I setup. For example, I want to audit all updates for Customers table. I used the following sql to update Customers table:

    DECLARE @CustomerID INT
    DECLARE @OrderNumber INT

    SET @CustomerID = 101
    SET @OrderNumber = 956

    UPDATE Customers
     SET OrderNumber = @OrderNumber
     WHERE CustomerID = @CustomerID


    Audit Log viewer in SQL Server Management Studio shows the update statement, but it doesn't show the values of parameters @CustomerID and @OrderNumber. I expected to see the values of paramaters somewhere, for example in 'Additional information' field. Is there any way to see parameters values, because without the values of paramaters auditing is not very useful.

    Thursday, December 03, 2009 7:36 PM

Answers

  •  

    Use Change data capture concept.  CDC capture all DML operation performed on the table.

    Enable CDC at DB level and table level.  Using this option you can capture all the DML operation.  


    Rajeev R
    Saturday, December 05, 2009 5:55 PM
  • Kazimir,
    Currently the Audit feature does not capture the statements in such detail, but I suggest that you give us your feedback at https://connect.microsoft.com/sql so that we can improve this in future.

    You may consider using CDC to trace the data change as Rajeev mentioned. CDC is a good tool for you to trace DML operations on a table. You can enable CDC on your table Customers as following:
    1. Enable CDC on your database
    USE <database_name>
    Go
    EXEC sys.sp_cdc_enable_db
    GO

    2. Enable CDC on your table
    EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'table_name',
    @role_name     = NULL,
    @supports_net_changes = 1
    GO

    3. DML operations.

    4. Check the changed data capture
    DECLARE @capture_instance_name varchar(50)
    DECLARE @from_lsn binary(10), @to_lsn binary(10)
    SELECT @capture_instance_name =capture_instance FROM cdc.change_tables where  source_object_id = OBJECT_ID('your_table_name')

    SET @from_lsn =
       sys.fn_cdc_get_min_lsn(@capture_instance_name)
    SET @to_lsn   = sys.fn_cdc_get_max_lsn()

    SELECT * FROM cdc.fn_cdc_get_all_changes_<capture_instance_name>(@from_lsn, @to_lsn, N'all')
    GO

    For more information, please refer to:
    Change Data Capture
    http://msdn.microsoft.com/en-us/library/bb522489.aspx
    cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/bb510627.aspx

    If you do not want to try this, you may have to consider using a server side trace. Please see this:


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Wednesday, December 09, 2009 7:30 AM

All replies

  •  

    Use Change data capture concept.  CDC capture all DML operation performed on the table.

    Enable CDC at DB level and table level.  Using this option you can capture all the DML operation.  


    Rajeev R
    Saturday, December 05, 2009 5:55 PM
  • Kazimir,
    Currently the Audit feature does not capture the statements in such detail, but I suggest that you give us your feedback at https://connect.microsoft.com/sql so that we can improve this in future.

    You may consider using CDC to trace the data change as Rajeev mentioned. CDC is a good tool for you to trace DML operations on a table. You can enable CDC on your table Customers as following:
    1. Enable CDC on your database
    USE <database_name>
    Go
    EXEC sys.sp_cdc_enable_db
    GO

    2. Enable CDC on your table
    EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'table_name',
    @role_name     = NULL,
    @supports_net_changes = 1
    GO

    3. DML operations.

    4. Check the changed data capture
    DECLARE @capture_instance_name varchar(50)
    DECLARE @from_lsn binary(10), @to_lsn binary(10)
    SELECT @capture_instance_name =capture_instance FROM cdc.change_tables where  source_object_id = OBJECT_ID('your_table_name')

    SET @from_lsn =
       sys.fn_cdc_get_min_lsn(@capture_instance_name)
    SET @to_lsn   = sys.fn_cdc_get_max_lsn()

    SELECT * FROM cdc.fn_cdc_get_all_changes_<capture_instance_name>(@from_lsn, @to_lsn, N'all')
    GO

    For more information, please refer to:
    Change Data Capture
    http://msdn.microsoft.com/en-us/library/bb522489.aspx
    cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/bb510627.aspx

    If you do not want to try this, you may have to consider using a server side trace. Please see this:


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Wednesday, December 09, 2009 7:30 AM
  • Thanks for your replies. CDC will work in my case, but showing parameter values would be helpful in Audit feature.
    Wednesday, December 09, 2009 8:34 PM
  • Alex,
    Yes, I agree. You can submit your feedback to us at the connect website. Thank you!


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, December 10, 2009 6:11 AM
  • Hi All,

    I am finding difficulty in filtering Application Accounts while audting... For Example,

    We have,

    support group - Domain\L3Support
    Application Accounts - Domain\APPSA, Domain\AppUserName

    Now I need to log/audit only support specific acitivities on to one of my object. Is this possible? When I specify Domain\L3Support as Prinicipal Name its not logging anything. But DBO/Public works fine for me.

    Thanks,
    -SreejitG
    Monday, December 14, 2009 7:19 AM
  • Hi Charles Wang,

    I tried your solution.

    In my case i need to track a table data When,What and Who.
        (when it selected ,update,Insert or deleted   
        What is selected ,update,Insert or deleted    
        Who selected ,update,Insert or deleted  )  
    for all tables in database.

    I created DatabaseAuditSpecification , then i noticed it doesn't have any information about who & what.

    After i saw this forum, i tried CDC , still i am not getting any information about when,what & who.

    Could you please tell me what is the best solution for this auditing.

    Also how to link DatabaseAuditSpecification  & CDC tables.

    The user informations are stored in  aspnet_Users table

    Thanks.
    Eva
    • Edited by Masi Thursday, January 07, 2010 4:30 PM
    Thursday, January 07, 2010 12:58 AM
  • Hi Eva,
    For your requirement, it requires very detail information. The server side trace should help but it may have some side effect on your server performance. Anyway I recommend that you perform a test to check if this method works well for you.
    You can refer to:
    Server-Side Tracing and Collection
    http://technet.microsoft.com/en-us/library/cc293613.aspx

    Of course if your intention is to trace those operations from your application, I recommend that you implement the audit function at your application level.

    Best regards,
    Charles Wang
    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, January 07, 2010 7:24 AM
  • Hi Charles,

    Thanks for your timely responce.

    After analysed Server - Side Tracing we thought to stick on CDC just adding a column modified by in table.

    But i do have couple of questions on CDC.

    1.For table structure change (Add/Delete column) what will happen to the CDC table.
    2.How do i get time stamp in CDC table.

    Thanks.
    Eva

    Friday, January 08, 2010 12:03 AM
  • 1. For DDL changes , you can use stored proc sys.sp_cdc_get_ddl_history or directly query the system catalog cdc.ddl_history

    2. You can make use of cdc.lsn_time_mapping system table or system function sys.fn_cdc_map_lsn_to_time to get the time of your transaction .
    You need to know the lsn number for using the above function which can be obtained from cdc.change_tables .

    Have a look at all the available stored procs , tables and functions related to CDC from this link
    http://technet.microsoft.com/en-us/library/bb500353.aspx


    Thanks, Leks
    Friday, January 08, 2010 12:59 AM
  • Hi Leks,

    Thanks for your quick responce.

    For example table1 has cdc table as cdc.db.table1_CT.
    If we add one more column to table1 is system going to update cdc.db.table1_CT or do we have to manually update cdc.db.table1_CT.
    or
    if we modify column name or data type is system going to upadte on cdc.db.table1_CT.

    Thanks.
    Eva
    Friday, January 08, 2010 1:24 AM
  • You cannot do anything on the system tables , adhoc updates to the system tables is not allowed.
    The table will be updated by system.


    Thanks, Leks
    Friday, January 08, 2010 1:29 AM
  • Hi,

    I added a new column to table1 , but the changes are not reflected to cdc.db.table1_CT.

    Also i modified a column name , i got an error message says

    Error where encounted during saving process.Some database object were not saved
    'table1' table
    - Unable to rename column from 'x' to 'xy'. 
    Cannot alter column 'x' because it is 'REPLICATED'.
    A severe error occurred on the current command.  The results, if any, should be discarded.

    Can any one help me ASP.

    Thanks.
    Eva

    Friday, January 08, 2010 5:00 PM
  • Can you try to disable the cdc on that table , alter your column and then enable cdc on that table.

    Again to make it clear , When you alter your table structure it gets updated in the ddl_history but to make that column appear under the captured_columns you have to disbale and re-enable the cdc on that particular table . Sorry to have this missed in my previous post.


    EXECUTE sys.sp_cdc_disable_table
        @source_schema = N'dbo',
        @source_name = N'table_1',
        @capture_instance = N'all';
    GO
    EXECUTE sys.sp_cdc_enable_table
        @source_schema = N'dbo'
      , @source_name = N'table_1'
      , @role_name = N'cdc_Admin';
    GO



    Thanks, Leks
    Friday, January 08, 2010 6:24 PM
  • Hi Leks,

    I tried Disable / Enabe CDC.

    That works good but it deleted all the previous data(oh no).

    Is there any way we can keep the previous data .

    Thanks.
    Eva
    Friday, January 08, 2010 7:10 PM
  • Have you tried to import / export those data to a temporary table or something like that.
    Run a select * from the table and store it in excel or other database tables.

    Thanks, Leks
    Friday, January 08, 2010 8:24 PM
  • Hi Leks,

    I don't like this work around. It's going to be lot of work to change the schema of the table.

    This makes the whole CDC concept not usable. Back to trigger after a long reserch with SQL Audit & CDC.

    Is there any new way implimented in SQL server 2010.

    Thanks.
    Eva

    Friday, January 08, 2010 8:38 PM
  • Hi all,

     

    Why after a create a server audit and a database audit specification on a select on my table the audit log dont show my SELECTS?

     

     

     

    Dont have any way to put a a SCHEMA in my database audit specification?

     

    Tks,

     

    Elber

    Saturday, December 18, 2010 1:23 PM
  • Actually, yes database audit specification is not useful at all because of this parameters issue.
    Did you solve your problem?
    I'm trying to find another tool or something.
    Please reply if you have the solution.

    Regards,
    Nemtallah Dib karam
    Beirut, Lebanon
    Thursday, October 27, 2011 3:31 PM
  • Monday, October 31, 2011 8:20 PM