Email alerts when new databases are created in SQL Server

Chad Churchwell

Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication. He has been in IT for 14 years, working with SQL Server for 10 years, and is currently a enior DBA Consultant with Pragmatic Works. He is active in the community speaking at several SQL Saturday events, as well as maintaining a blog atwww.sqlchad.com.

http://www.mssqltips.com/sqlservertip/2864/email-alerts-when-new-databases-are-created-in-sql-server/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2013112

USE master
GO
CREATE TRIGGER trg_DDL_CreateDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @results varchar(max)
SET @results = 
  (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
GO

USE master
GO
CREATE TRIGGER trg_DDL_CreateDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @results varchar(max)
declare @subjectText varchar(max)
SET @subjectText = 'DATABASE Created on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() 
SET @results = 
  (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'SQLAlerts',
 @recipients = 'SQLDBAGROUP@YOURDOMAIN.COM',
 @body = @results,
 @subject = @subjectText
GO