Auditing Database create or remove

Question

Hi,

I'd like to monitor SQL database creation or removal so I can generate an event ID onto my NT application logs.

How can I do that easily?

Thanks,

Cédric

Win 2008R2, SQL 2008 R2

Answer 1

Hello Cédric,

You could check the default trace for such events or you can create a DLL trigger to audit it ; see DDL Triggers , there is a sample for a CREATE_DATABASE trigger.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

USE [master]
GO
CREATE SERVER AUDIT [ApplicationLogAudit]
TO APPLICATION_LOG
WITH (QUEUE_DELAY = 1000,ON_FAILURE = CONTINUE)
GO
CREATE SERVER AUDIT SPECIFICATION [ApplicationLogAudit_Spec]
FOR SERVER AUDIT [ApplicationLogAudit]
ADD (DATABASE_CHANGE_GROUP)
--This event is raised when a database is created, altered, or dropped. 
WITH (STATE = ON)
GO
--Server-Level Audit Action Groups
--http://technet.microsoft.com/en-us/library/cc280663.aspx
ALTER SERVER AUDIT [ApplicationLogAudit] WITH (STATE = ON)

---

Jon

Answer 3

What if someone restore the database I do no think DDL triggers will capture it..

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 4

Hi, BACKUP_RESTORE_GROUP would be better for the server audit. DDL triggers cannot catch the restore event. Janos

---

There are 10 type of people. Those who understand binary and those who do not.

My Blog | Hire Me

Answer 5

As posted above the op can add which events they are interested by visiting the link: http://technet.microsoft.com/en-us/library/cc280663.aspx

They may well choose to have both DATABASE_CHANGE_GROUP and BACKUP_RESTORE_GROUP in the audit, but there may be other events they are interested in e.g. SERVER_STATE_CHANGE_GROUP

---

Jon

Answer 6

Hi,

Thanks it helped a lot. I managed to get the event ID 33205 with: statement:CREATE DATABASE [test08] ON  PRIMAR. Actually I get several 33205 following a database creation.

Can I get a specific ID that I would choose, based on the statement (CREATE DATABASE) so I get a unique ID for a database creation.

I'm asking because We are using System Center Operation Manager and I can raise an alert based on the NT Application log I