In this article I will explain with an example, how to create account and profile for sending emails in SQL Server.
 
 

Creating Account for sending emails in SQL Server

The sysmail_add_account_sp Stored Procedure is used to add Account to SQL Server.
This Stored Procedure accepts necessary parameter which contains Mail Server settings as UserName, Password, Port and EnableSsl etc.
Note: It is necessary to use the sender’s email address credentials while defining the Gmail SMTP Server Credentials as the sender’s email address must be same as the Gmail Username specified in credentials.
 
To create an Account, simply copy, paste, edit (according to your settings) and execute the following SQL query.
Note: Make sure you enter valid Gmail SMTP Server Credentials.
 
EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = 'Mudassar_Mail_Account'
   ,@description = 'Send emails using SQL Server Stored Procedure'
   ,@email_address = 'youremail@gmail.com'
   ,@display_name = 'Mudassar Ahmed Khan'
   ,@replyto_address = 'youremail@gmail.com'
   ,@mailserver_name = 'smtp.gmail.com'
   ,@username = 'youremail@gmail.com'
   ,@password = 'GMAIL PASSWORD'
   ,@port = 587
   ,@enable_ssl = 1
GO
 
Note: For complete documentation on sysmail_add_account_sp Stored Procedure, please refer here.
 
 

Creating Profile for sending email in SQL Server

The Profile contains information about Account for which the Account needs to be added to a Profile as it is necessary to provide the name of the Profile while sending email using sp_send_dbmail Stored Procedure in SQL Server.
The sysmail_add_profile_sp Stored Procedure is used to create Profile to SQL Server.
To create a profile, simply copy, paste, edit (according to your settings) and execute the following SQL query.
EXEC msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Mudassar_Email_Profile'
   ,@description = 'Send emails using SQL Server Stored Procedure'
GO
 
Note: For complete documentation on sysmail_add_profile_sp Stored Procedure, please refer here.
 
 

Adding Account to Profile for sending email in SQL Server

The sysmail_add_profileaccount_sp Stored Procedure is used to add Account to Profile.
To add account to profile, simply copy, paste, edit (according to your settings) and execute the following SQL query.
EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Mudassar_Email_Profile'
   ,@account_name = 'Mudassar_Mail_Account'
   ,@sequence_number = 1
GO
 
Note: For complete documentation on sysmail_add_profileaccount_sp Stored Procedure, please refer here.
 
 

Downloads

Download Code