When it comes to configuring SQL Server Database Mail, this article is ideal for novices. Additionally, it can be utilized in documentation or training sessions for novice developers.

Verify the Current SQL Server SMTP Configuration
Goals
to confirm whether SQL Server Database Mail has any SMTP profiles set up.

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.

HostForLIFEASP.NET SQL Server 2022 Hosting