info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



Server-Level Trigger to notify on any Drop Database
by BF (Principal Consultant; Architecture; Engineering)
2019-02-28







T-SQL:


USE [master]
GO

/****** Object: DdlTrigger [DropDatabaseNotifier] Script Date: 2/28/2019 2:08:24 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [DropDatabaseNotifier]
ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
DECLARE
@srvname NVARCHAR(255),
@dbname NVARCHAR(255),
@login NVARCHAR(255),
@time NVARCHAR(255),
@msg NVARCHAR(MAX);

SELECT
@srvname = EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]','NVARCHAR(255)'),
@dbname = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(255)'),
@time = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','NVARCHAR(255)'),
@login = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(255)');

SELECT @msg =
N'User ' + QUOTENAME(COALESCE(@login, N'<unknown>'))
+ N' has dropped the database '
+ QUOTENAME(COALESCE(@dbname, N'<unknown>')) + N' on '
+ QUOTENAME(COALESCE(@srvname, N'<unknown>')) + N' at '
+ QUOTENAME(COALESCE(@time, N'<unknown>')) + N'. '
;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = N'DBLMAIL',
@recipients = N'ops@ops.com',
@subject = N'Database dropped !!!',
@body = @msg;
END
GO

ENABLE TRIGGER [DropDatabaseNotifier] ON ALL SERVER
GO


Email:

User [username] has dropped the database [dbname] on [servername] at [eventtime].



Script out the Trigger and SSMS:

SELECT name, OBJECT_DEFINITION ([object_id]) as 'Object Definition' FROM sys.server_triggers

SSMS..Server Objects..Triggers



Resources:

EVENTDATA (Transact-SQL)

Microsoft SQL Server XML Schemas

SQL Server Event Data Schema

SQL Server Event Data Schema - Current Version