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.

Answer 1

 

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

Answer 2

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:

Using SQL Trace
http://msdn.microsoft.com/en-us/library/ms191443.aspx

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help

Answer 3

Thanks for your replies. CDC will work in my case, but showing parameter values would be helpful in Audit feature.

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

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

Answer 14

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

Answer 15

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

Answer 16

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

Answer 17

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

Answer 18

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

Answer 19

Try this one:

http://support.microsoft.com/kb/967552/