audit in SQL 2005

Expensive friends,

I am new in the T-SQL development and would like to count on aid of all. Good my problem in question is that I am needing to develop a system of audit in SQL 2005, however does not know if I am in the certain way. Already I created some codes, however they are Triggers of each table of data, then I found that it is not very practical, therefore to need to control 200 tables I will have that to make a Trigger for each one. Some to develop that it reads me could give a light to me. Already I verified that it is possible to use DDL for events of the Database (ex.DDL_DATABASE_LEVEL_EVENTS), however did not find the same for tables.

I am thankful for the aid of all!

You have several methods of creating auditing.

1. Triggers on EACH table, write data to audit table
2. Third party product that will create an audit log from the Transaction Log activity
3. Require all data activity to use stored procedures, and add auditing code to stored procedures

You are correct, there is not a database wide event that reacts to data changes.

(And we are not 'too' expensive!)

|||

You can automate the creation of DML audit triggers using this product:

http://www.apexsql.com/sql_tools_audit.asp

If you are looking to audit table DDL changes, then you should use the DDL_TABLE_EVENTS event group, more info is available at this link:

http://msdn2.microsoft.com/en-us/library/ms191441.aspx

Note that you don't have to write a DDL trigger for every table - you can write one trigger for all tables and inside the trigger you can parse the XML returned by the EVENTDATA function to extract the audit data you need.

Chris

|||

Arnie Rowland,

it wanted to be thankful for its aid. I find that in this in case that this problem must very be common between the DBA, who knows in the next version to SQL we let us can count with whom we it helps in this difficult task, that is to take care of of data base.

Not yet I will discourage and count on the contribution of the other friends.

Thanks a lot.

|||

Chris,

I Am thankful for me to pass these links, I wait that he helps me in uncurling of this task. As I commented I am not one expert in T-SQL, therefore all aid always is welcome.

I am thankful very for the aid.