Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am trying to establish auditing for a SQL Server database without using the trigger approach that is commonly used. I thought I could use CDC and Sql Server auditing. But both technologies do not provide all necessary information in one place. Change Data Capture provides only the data that has changed as complete row in a shadow table but not who did the change. Auditing has a column 'statement' that only shows the statament with parameter placeholders, but therefore the login user who made the change.

But I want to know Who changed what in one place. Is it possible to this with those techniques? Or do I have to use tre trigger approach?

share|improve this question
add comment

2 Answers 2

Are you against having a LastUpdatedBy column in your table that is maintained by an insert/update trigger? If not, then that plus CDC will get you where you want to go. Otherwise, no. You'd have to correlate the statement in the audit with the data from the CDC table which could be non-trivial.

share|improve this answer
    
It is a very old application that uses a LastUpdatedBy Column rather inconsequently. So i guess that I have to go with triggers. –  Marius Feb 29 '12 at 9:14
add comment

SQL Server Audit has the session_server_principal_name column which shows Server principal for the session.

If you use audit file as target, you can test it using code such as:

SELECT event_time , 
   action_id , 
   session_server_principal_name, 
   server_instance_name , 
   database_name , 
   schema_name , 
   object_name , 
   statement FROM sys.fn_get_audit_file( 'D:\TestAudits\*.sqlaudit' , DEFAULT , DEFAULT)

Make sure you replace the audit file path

SQL Server Audit Records

enter image description here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.