Alert in MS SQL when new database is created or when database is deleted

Execute the below SQL query to create a trigger on the MASTER database.  An email will be sent when ever a new database is created on the server or when a database is deleted.

 

CREATE TRIGGER trgCreateDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @Subj NVARCHAR(255)
DECLARE @MailBody NVARCHAR(MAX)

SET @Subj = @@SERVERNAME + ‘ – Database Created’
SELECT @MailBody =
‘TSql Command: ‘ + EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’) + CHAR(13) + CHAR(10) +
‘Login Name: ‘ + EVENTDATA().value(‘(/EVENT_INSTANCE/LoginName)[1]’,’nvarchar(max)’)
EXEC msdb..sp_send_dbmail
@from_address = ‘mail@34.231.97.77’,
@recipients = ‘admin@34.231.97.77’,
@Subject = @Subj,
@body = @MailBody
GO

CREATE TRIGGER trgDropDatabase
ON ALL SERVER
FOR DROP_DATABASE
AS
DECLARE @Subj NVARCHAR(255)
DECLARE @MailBody NVARCHAR(MAX)

SET @Subj = @@SERVERNAME + ‘ – Database Dropped’
SELECT @MailBody =
‘TSql Command: ‘ + EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’) + CHAR(13) + CHAR(10) +
‘Login Name: ‘ + EVENTDATA().value(‘(/EVENT_INSTANCE/LoginName)[1]’,’nvarchar(max)’)
EXEC msdb..sp_send_dbmail
@from_address = ‘mail@34.231.97.77’,
@recipients = ‘admin@34.231.97.77’,
@Subject = @Subj,
@body = @MailBody
GO

Leave a Reply

Your email address will not be published. Required fields are marked *

Bitnami