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