How to create a DDL Trigger to notify on New Database Creation

The following code snippet will create a DDL trigger than notifies a specified group upon the creation of a new database (applies to SQL 2005 and 2008). This assumes that the SQL mail has already been configured and in the below example is defined as the “SQL Monitor” profile;

CREATE TRIGGER [DDL_CREATE_DATABASE_EVENT]
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @bd varchar(max)
DECLARE @tsql varchar(max)
DECLARE @sub varchar (max)
DECLARE @newDB varchar (max)
DECLARE @fint int
Set @tsql = EVENTDATA().value
('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)')
SET @fint = CHARINDEX('[', @tsql) - 1
SET @newDB = SUBSTRING(@tsql, CHARINDEX('[', @tsql), CHARINDEX(']', @tsql) - @fint)
SET @bd = '<b>PLEASE REMEMBER TO DO A FULL BACKUP OF ' + @newDB + '!!</b>&lt;br>&lt;br>
UserName: ' + UPPER(SUSER_NAME()) + '&lt;br>
ServerName: ' + @@SERVERNAME + '&lt;br>
Time: '	+ CONVERT(varchar(25),Getdate()) + '&lt;br>
HostName: ' + HOST_NAME() + '&lt;br>
Database: <b>' + @newDB + '</b>&lt;br>&lt;br>
T-SQL: ' +  @tsql + '&lt;br>&lt;br>&lt;br>'
SET @sub  = 'A new database ' + @newDB + 'has been created on ' + HOST_NAME() 
BEGIN
-- PRINT 'Make sure you have informed all DBAs before creating databases. This event has been logged'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL Monitor',
@recipients = 'supportGroup@domain.com',
@subject = @sub,
@body_format = 'HTML',
@importance = 'High',
@body = @bd
END
GO
ENABLE TRIGGER [DDL_CREATE_DATABASE_EVENT] ON ALL SERVER
GO

Final Important Note! If you have environments (such as SharePoint) which auto-create their own content databases, the service account used to generate the DBs will need to have execute permissions to the above trigger.