SQL Server: Send alerts via email

Sometimes we want to send an email when something suspicious happens in a database. SQL Server is able to natively send emails.

Example: We want to receive an alert every time a user is deleted, so that we can immediately check if this happened for a good reason or not.

We could do this using external tools that regularly connect to the database and perform some checks about the most important data. However this would be overcomplicated, and requires the installation of some tools.

Trigger code

Here we’ll see how to achieve the same result with a trigger.

CREATE TRIGGER AuditUserDelete ON dbo.user
    FOR DELETE
AS
    DECLARE @UserId INT
    SELECT @UserId = id FROM deleted
    EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = 'DBA',
        @recipients = 'john.smith@example.com',
        @body = 'User id: ' + @UserId,
        @subject = 'ALERT: User was deleted'
GO

Here’s what happens when a user is deleted:

  • The AuditUserDelete is fired;
  • The deleted user’s id (from the deleted table) is copied into the @UserId variable;
  • msdb.dbo.sp_send_dbmail sends an email.

Reference

SQL Server documentation:

Other articles: