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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy