snippetsqlMinor

How to know who dropped a database

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

knowdroppedwhodatabasehow

Problem

I have in the SQL server a lot of databases, but one of them has been dropped, so I need to create a trigger to send me an email when someone try to drop another database. The e-mail should contain the user name and the the name of the database.

Solution

@Kin is right about getting this info from the default trace, if it is in fact still there (depends on how busy your server is, and how long ago this happened).

To get an e-mail immediately, though, instead of having to remember to check the default trace all the time just in case something happened, you can do this from a DDL trigger. Database Mail config is out of scope for the question, I think, so I'll leave that part to you (there is a Q & A here).

ALTER TRIGGER DroppinDatabasesAwYeah
ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
  DECLARE 
    @name    NVARCHAR(255), 
    @login   NVARCHAR(255),
    @msg     NVARCHAR(MAX);

  SELECT 
    @name  = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(255)'),
    @login = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(255)');

  SELECT @msg = N'It appears that '
    + QUOTENAME(COALESCE(@login, N''))
    + N' has dropped the database ' 
    + QUOTENAME(COALESCE(@name, N'')) + N'.';

  EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'default',
    @recipients = N'you@yourdomain.com',
    @subject = N'Somebody done dropped a database',
    @body = @msg;
END
GO

Code Snippets

ALTER TRIGGER DroppinDatabasesAwYeah
ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
  DECLARE 
    @name    NVARCHAR(255), 
    @login   NVARCHAR(255),
    @msg     NVARCHAR(MAX);

  SELECT 
    @name  = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(255)'),
    @login = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(255)');

  SELECT @msg = N'It appears that '
    + QUOTENAME(COALESCE(@login, N'<unknown>'))
    + N' has dropped the database ' 
    + QUOTENAME(COALESCE(@name, N'<unknown>')) + N'.';

  EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'default',
    @recipients = N'you@yourdomain.com',
    @subject = N'Somebody done dropped a database',
    @body = @msg;
END
GO

Context

StackExchange Database Administrators Q#63676, answer score: 8

Revisions (0)

No revisions yet.