question

Shashahan avatar image
Shashahan 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
sql-server-2008-r2audit
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.