SQL Server has a database mail option that you can use to send mail from the database server.

For example, you can get notifications when SQL Server Agent jobs finish running or fail, or when there’s a high-severity error, etc.

When Database Mail is Not Configured

In SQL Server, mail is sent by executing the sp_send_dbmail stored procedure in the msdb database.

Here’s an example:

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'DB Admin Profile',  
    @recipients = '[email protected]',  
    @body = 'Your favorite SQL Server Agent job just failed',  
    @subject = 'SQL Server Agent Job: FAILED';

However, this assumes that SQL Server is configured to send mail.

If you try to send mail from SQL Server, but you get an error like the following, it means you don’t currently have Database Mail enabled.

Msg 15281, Level 16, State 1, Procedure msdb.dbo.sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', search for 'Database Mail XPs' in SQL Server Books Online.

Enable Database Mail XPs

Before you start sending mail from the server, you need to ensure you’ve got Database Mail XPs enabled.

This is pretty easy to do (although, Microsoft recommends that advanced options such as this should be changed only by an experienced database administrator or certified SQL Server technician).

Here’s how to enable Database Mail XPs:

EXEC sp_configure 'show advanced options', '1';
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE
GO

Result:

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Commands completed successfully.
Commands completed successfully.

Create a Mail Account, Profile, etc

Database Mail is sent via a profile, rather than a user account directly.

To send email with Database Mail you need to create a Database Mail account, a Database Mail profile, add the account to the profile, and then grant a user access to that profile. The user needs to be on the msdb database.

The T-SQL code to do this could look something like this:

-- Switch to the msdb database
USE msdb;

-- Create a user on the msdb database
CREATE USER Marge FOR LOGIN Marge;

-- Create a Database Mail account  
EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'DB Admin',  
    @description = 'Mail account for admin emails.',  
    @email_address = '[email protected]',  
    @replyto_address = '[email protected]',  
    @display_name = 'DB Automated Mailer',  
    @mailserver_name = 'smtp.example.com',
    @port = 25;  
  
-- Create a Database Mail profile  
EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'DB Admin Profile',  
    @description = 'Profile for admin emails.';  
  
-- Add the account to the profile  
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'DB Admin Profile',  
    @account_name = 'DB Admin',  
    @sequence_number = 1;
  
-- Grant the msdb user access to the Database Mail profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'DB Admin Profile',
    @principal_name = 'Marge',
    @is_default = 1;

You’ll need to replace the various details with your own. This also assumes that you specify a mail server that works.

Once you’ve done that, you should be able to send mail with the msdb.dbo.sp_send_dbmail stored procedure.