Configuring Database Mail On Microsoft SQL Server

A walkthough of the steps to configure Database Mail on SQL Server done 2 ways. First using the SSMS dialogs and UI, then using TSQL Scripts.

[CharlieArehart]

Thanks as always, Steve. It might help some to hear that this "database mail" option is not available in SQL Server Express edition.

And sure, that's likely due to its lack of the SQL Server Agent feature (as much as being a hold-back from non-paying users), since the alerts and operators (that can send email) are only in the Agent feature. But as you said, sending email out of an SP could have been helpful even for Express users.

[omichaelSQL]

did you do this on a VM or on a local pc? if not would the process work on a VM? Thanks

[SteveStedman]

Here is the script I used in the video:
USE msdb;
GO

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

-- Declare variables for the email account and profile
DECLARE @ProfileName NVARCHAR(128) = 'YourProfile'; -- Change this to your desired profile name
DECLARE @AccountName NVARCHAR(128) = 'Your Account'; -- Change this to your desired account name
DECLARE @DisplayName NVARCHAR(128) = 'Your Display Name'; -- Change this to the display name you prefer
DECLARE @MailServer NVARCHAR(256) = 'mail.xyz.com'; -- Change this to your SMTP server address
DECLARE @Password NVARCHAR(128) = 'Your Password Here'; -- Change this to your SMTP password
DECLARE @Port INT = 587; -- Change this to your SMTP server port
DECLARE @EnableSSL BIT = 0; -- Set to 1 if SSL is required; otherwise, set to 0

-- Add a new email account
EXEC
@account_name = @AccountName,
@email_address = @EmailAddress,
@display_name = @DisplayName,
@mailserver_name = @MailServer,
-- Include the next two lines if authentication is required
@username = @Username,
@password = @Password,
@port = @Port,
@enable_ssl = @EnableSSL;

-- Add a new profile
EXEC
@profile_name = @ProfileName,
@description = 'Profile used for sending emails from SQL Server';

-- Associate the account with the profile
EXEC
@profile_name = @ProfileName,
@account_name = @AccountName,
@sequence_number = 1; -- Defines the order of preference if multiple accounts are added

-- Grant access to the profile to DatabaseMailUserRole in msdb
EXEC
@profile_name = @ProfileName,
@principal_name = 'public', -- Grants access to all users. Change as needed.
@is_default = 1; -- Makes this the default profile for the specified principal