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
|
|
|
|
|
|