• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

Sql server Audit Table

I want to audit changes in all tables in the database, with all audit records going to the same table.
Something like
            Audit_ID int identity(1,1) PRIMARY KEY,
 
            OperationGUID uniqueidentifier, --action_id
 
            AppName     varchar(128) DEFAULT APP_NAME(), --application
 
            TableName varchar(128), --table_name
 
            KeyValue  varchar(128),
 
            Operation CHAR(1), --action_type
 
            DateChanged datetime DEFAULT GETDATE(), --action_datetime
 
            UserName varchar(128) DEFAULT SYSTEM_USER, --username
 
            ComputerName varchar(128) DEFAULT HOST_NAME(), --computer
 
            FieldName varchar(128), --column_name
 
            OldValue nvarchar(1024), --data_old
 
            NewValue nvarchar(1024) --data_new


INSERTs OldValue should be NULL,
for DELETEs NewValue should be NULL,
for UPDATEs I would need both old and new values (but only for the fields that changed).
if  someone already has the actual code ready then please pass it to me it will be greatly appreciated
0
sas77
Asked:
sas77
  • 2
  • 2
3 Solutions
 
dportasCommented:
Have you looked at the Change Data Capture feature? It was designed to fulfil exactly your requirement:
http://msdn.microsoft.com/en-us/library/bb522489.aspx
0
 
sas77Author Commented:
dportas ,

Thanks you very much, but i need something very urgent so if  someone already has the actual code ready then please pass it to me it will be greatly appreciated
0
 
dportasCommented:
It takes about 30 seconds to enable CDC for a table and from that point the capture of audit data happens automatically. Example code here:
http://technet.microsoft.com/en-us/library/cc627369.aspx

Note:

1) You need Enterprise Edition.

2) You need to understand and test the requirements re performance, logging, security, etc. The same applies to ANY form of audit for that matter. TANSTAAFL
0
 
sas77Author Commented:
That is the problem we are using The instance of SQL Server is the Standard Edition. Change data capture is only available in the Enterprise, Developer, and Enterprise Evaluation editions. So i need any alternate to do this .

Thank you
0
 
jagwynnCommented:
 
Here is a link to some code that you will have to customize a little and you would need to create a trigger for every table in your database. No the best way to go about auditing a database but this should get you started.  

http://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now