SQL Server Database Mail Setup and SMTP Configuration Guide

This article is perfect as a beginner’s reference for setting up SQL Server Database Mail. It can also be used in training sessions or documentation for new developers.

Check Existing SMTP Configuration in SQL Server

Purpose

To verify if any SMTP profiles are configured in SQL Server Database Mail.

USE msdb;
GO
SELECT * FROM dbo.sysmail_profile;

Create a New Database Mail Profile

Purpose

To create a new Database Mail profile in SQL Server that can be used to send emails via SMTP accounts.

Query

EXEC msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'MailCampaign',
    @description = 'Profile for sending SQL Server emails';

Explanation of Parameters

@profile_name = 'MailCampaign'
- This is the name of your Database Mail profile.
- A profile is like a container for SMTP accounts that SQL Server will use to send emails.
- In your query, 'MailCampaign' is the name you chose for the profile.
- You can change this to any name you want, for example: 'SQLMailer', 'AdminMail', etc.
@description = 'Profile for sending SQL Server emails'
- Optional text describing the purpose of this profile.
- Helps to identify the profile later if you have multiple profiles.

Create SMTP Configuration for Database Mail

Purpose

To configure an SMTP account in SQL Server that will be used by a Database Mail profile to send emails.

Query

EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = 'Mail Campaign',
    @description = 'SMTP account for MailCampaign',
    @email_address = '[email protected]', -- Your email Id
    @display_name = 'NoReply',                   -- Your display name in email
    @replyto_address = '[email protected]', -- Reply-to email Id
    @mailserver_name = 'smtp.sendgrid.net',      -- SMTP server
    @port = 587,                                 -- SMTP port (587 for TLS/STARTTLS)
    @username = 'apikey',                        -- SMTP username (SendGrid uses 'apikey')
    @password = 'SUCXUo5r3uCBZdlw',             -- SMTP password or API key
    @enable_ssl = 0;                             -- Enable SSL/TLS (1 = Yes, 0 = No)

Next Step: Map Account to Profile

After creating the SMTP account, you need to associate it with your Database Mail profile:

Query

EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'MailCampaign',
    @account_name = 'Mail Campaign',
    @sequence_number = 1;

Send a Test Email

Purpose

To verify that the SMTP configuration and Database Mail profile are working correctly by sending a test email.

Query

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'MailCampaign',
    @recipients = '[email protected]',
    @subject = 'Test Email',
    @body = 'Database Mail is now configured correctly!';

SQL Server Database Mail Setup Script

USE msdb;
GO
SELECT *
FROM dbo.sysmail_profile;
--Step 1:
EXEC msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'MailCampaign',
    @description = 'Profile for sending SQL Server emails';
--Step 2:
EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = 'Mail Campaign',
    @description = 'SMTP account for MailCampaign',
    @email_address = '[email protected]', -- Your email Id
    @display_name = 'NoReply',                   -- Your display name in email
    @replyto_address = '[email protected]', -- Reply-to email Id
    @mailserver_name = 'smtp.sendgrid.net',      -- SMTP server
    @port = 587,                                 -- SMTP port (587 for TLS/STARTTLS)
    @username = 'apikey',                        -- SMTP username (SendGrid uses 'apikey')
    @password = 'SUCXUo5r3uCBZdlw',             -- SMTP password or API key
    @enable_ssl = 1;                             -- Enable SSL/TLS (1 = Yes, 0 = No)
--Step 3:
EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'MailCampaign',
    @account_name = 'Mail Campaign',
    @sequence_number = 1;
--or
EXEC msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'MailCampaign',
    @principal_name = 'public',
    @is_default = 1;
--Step 4:
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'MailCampaign',
    @recipients = '[email protected]',
    @subject = 'Test Email',
    @body = 'Database Mail is now configured correctly!';
SELECT * FROM msdb.dbo.sysmail_event_log ORDER BY log_date DESC;
--Your Reference Script
SELECT * FROM msdb.dbo.sysmail_account;
SELECT * FROM msdb.dbo.sysmail_profile;
SELECT * FROM msdb.dbo.sysmail_profileaccount;
SELECT * FROM msdb.dbo.sysmail_event_log ORDER BY log_date DESC;

Explanation

1. Step 0: Check if any Database Mail profiles already exist.

2. Step 1: Creates a new profile (MailCampaign) for sending emails.

3. Step 2: Adds an SMTP account linked to the profile (using SendGrid example).

4. Step 3: Maps the SMTP account to the profile and optionally sets it as the default for all users.

5. Step 4: Sends a test email to verify the configuration.

6. Step 5: Checks the event log and reference tables to troubleshoot or confirm successful setup.