March 17, 2011 at 7:23 am
Hi,
I created server auditing specifications in SQL 2008R2 for several changes on users (password change, rights assignment) and it works fine. Now I want to create a database auditing specifications for create, alter and drop table. According to MSDN and Technet documentation I should use as audit action type 'DATABASE_OBJECT_CHANGE_GROUP'.
After applying this specification it only logs CREATE TABLE, no ALTER or DROP TABLE commands are logged with this audit setting.
It is very strange and I can't find other articles from people with the same problem. Hopefully anyone here can help.
Thanks. Regards, Rudi
April 6, 2011 at 3:42 pm
DDL triggers are probably what you need to use.
Check out these two links for information about DDL triggers and how to create and use them:
http://www.sqlbook.com/SQL-Server/DDL-Triggers-in-SQL-Server-34.aspx
April 7, 2011 at 5:35 am
Thank you.
I also found out that DDL triggers can also write to Windows System log: http://msdn.microsoft.com/en-us/library/ms186244.aspx
September 1, 2011 at 9:38 am
Similar issue folks - CREATE statements being logged, DROP statements not ?
January 24, 2012 at 7:34 am
The solution from this article solved the problem for me.
http://social.msdn.microsoft.com/Forums/is/sqlsecurity/thread/9d054247-3b4e-4794-92d0-04b94eddcd9f
January 25, 2012 at 8:17 am
I'm assuming you're using the SQL Audit feature in SQL 2008. If that's the case, try using the SCHEMA_OBJECT_CHANGE_GROUP rather than the DATABASE_OBJECT_CHANGE_GROUP.
Colleen
January 25, 2012 at 8:26 am
Audit classes (as per Colleen) : http://msdn.microsoft.com/en-us/library/cc280663.aspx
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply