asked
Audit all DML & DDL actions but exclude specific user
Hi, i'd like to know how i can audit all DML (SELECT, UPDATE, INSERT) and DDL (CREATE, ALTER, DROP) action to a specific database from all logins except one specific login. This audit should be written into the application log. In SQL Server 2012 i can achieve this with a filtered Audit like this: `([server_principal_name]<>'AD\user1234')` How can archive the same with SQL Server 2008R2? Best Regards, Shashahan
This site works by you voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer led to a solution, indicate this by clicking on the check mark next to that answer.
No, I don't think you can do that in the database audit specification. There is a (not so nice) workaround. You can explicitly name the logins you DO wish to audit for, and have a DDL trigger on server level to capture CREATE/ALTER/DROP login-events and have the trigger update the database audit specification.
Personally I would choose event notification to capture the DDL as it is an asynchronous and wont have any effect on the oltp activites. you wont be able to collect the DML that way though.
17 People are following this question.
