How to send notifications automatically in case any new database is created.

Today I faced a new quest that is to raise an alarm incase if any new database is created in the server.

I have scouted in Google as a first step J but didn’t find any script meeting my requirements so wrote a script of my own.

So the best way I thought of achieving this is through server level DDL trigger which gets fired automatically when anyone creates a new database. I used msdb.dbo.sp_send_dbmail inside the trigger to send mail to respective recepients. In the mail being sent I need information like when the database was created, what is the recovery model of it, who is the owner of the database, collation. Based on requirement we can get other information as well.

IF EXISTS (SELECT * FROM sys.server_triggers

    WHERE name = 'Alert_Database_Creation')

DROP TRIGGER Alert_Database_Creation

ON ALL SERVER;

GO

CREATE TRIGGER Alert_Database_Creation

ON ALL SERVER

FOR CREATE_DATABASE

AS

Declare @sub varchar(2000),@srv varchar(2000),@dbname varchar(2000),@bod varchar(2000)
Declare @recoverymodel varchar(2000),@collation varchar(2000),@datecreated varchar(2000),@owner varchar(255)

select @srv= @@SERVERNAME

select @dbname = name,@owner = SUSER_SNAME(owner_sid), @collation = collation_name,@datecreated =
(SELECT CONVERT(VARCHAR(20), create_date, 100))
,@recoverymodel= recovery_model_desc from sys.databases
where (DATEPART(DD,create_date) = DATEPART(DD,GETDATE())) AND (DATEPART(YYYY,create_date) = DATEPART(YYYY,GETDATE()))
AND (DATEPART(MM,create_date) = DATEPART(MM,GETDATE()))
 

    select @sub = 'New Database Has Been Created in server '+ @srv

    Select @bod = 'Database Name    : '+ UPPER(@dbname) + Char(13)+'Database Owner  : '+@owner+ CHAR(13)+'Recovery Model  : '+@recoverymodel

    +CHAR(13)+'Collation                 : '+@collation+CHAR(13)+'Created Date        : '+@datecreated

    EXEC msdb.dbo.sp_send_dbmail

      @profile_name = 'profile name',

      @recipients = 'email@address’,

      @body = @bod,

      @subject =@sub

Thanks!!