Setting Up the Database Mail with Profile and Account

Context

Sending emails using MS SQL is used for MS SQL maintenance and alert follow-ups. This procedure is mainly intended for technicians.

 

Steps

Creating an Email Account

1. Open SSMS: 

2. Connect to the SQL server which contains the databases.

 

3. Click on new script and launch the following script:

USE [master]
GO

EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE
GO

EXEC sp_configure 'Database Mail XPs',1
RECONFIGURE
GO

ALTER DATABASE [MSDB] SET  ENABLE_BROKER WITH NO_WAIT
GO

-- Ajout du profil
IF NOT EXISTS (
	SELECT	*
	FROM	msdb.dbo.sysmail_profile 
	WHERE	name = 'SQLAlerts'
)
BEGIN
	EXEC msdb.dbo.sysmail_add_profile_sp
		@profile_name = 'SQLAlerts',
		@description = 'Alerts Database administrator'

	EXEC msdb.dbo.sysmail_add_principalprofile_sp
		@profile_name = 'SQLAlerts',
		@principal_name = 'public',
		@is_default = 1 ;
END
GO

IF NOT EXISTS (
	SELECT	*
	FROM	msdb.dbo.sysmail_account
	WHERE	name = 'SQLAccount'
)
BEGIN
	-- Ajout de l'Account
	/*************************************************************************
	 ** À CONFIGURER - remplacer les x par les bonnes valeurs
	 *************************************************************************/
	EXEC msdb.dbo.sysmail_add_account_sp  
		@account_name = 'SQLAccount',  
		@description = 'Mail account for administrative e-mail.',  
		@email_address = 'Support@xxxx.ca',  
		@display_name = 'Support',  
		@username = 'xxxxxxxxxxxx',
		@password = 'xxxxxxxxxxxx',
		@mailserver_name = 'xxxxxxxxxxxx.smtp.com' ;

	-- Ajout de account au profil
	EXEC msdb.dbo.sysmail_add_profileaccount_sp
		@profile_name = 'SQLAlerts',
		@account_name = 'SQLAccount',
		@sequence_number = 1
END
GO

4. Replace the xxxxxxxxxxxx in the @username, @password, and @mailserver_name variables for the accurate values.
   • @username: The account that will send emails.
   • @password: The password of this account.
   • @mailserver_name: The email server of this account.

 

Last modification: September 19, 2024