Database Mail in SQL Server

Database Mail in SQL Server

Database Mail has four components.

1. Configuration ComponentConfiguration component has two sub components. One is the Database Mail account, which contains information such as the SMTP server login, Email account, Login and password for SMTP mail.
   
   The Second sub component is Database Mail Profile. Mail profile can be Public, meaning members ofDatabaseMailUserRole in MSDB database can send email. For private profile, a set of users should be defined.
2. Messaging ComponentMessaging component is basically all of the objects related to sending email stored in the MSDB database.
3. Database Mail ExecutableDatabase Mail uses the DatabaseMail90.exe executable to send email.
4. Logging and Auditing componentDatabase Mail stores the log information on MSDB database and it can be queried usingsysmail_event_log.

Database Mail Configuration

Step 1

Before setting up the Database Mail profile and accounts, we have to enable the Database Mail feature on the server. This can be done in two ways. The first method is to use Transact SQL to enable Database Mail. The second method is to use a GUI.

In the SQL Server Management Studio, execute the following statement.

use master

go

sp_configure 'show advanced options',1

go

reconfigure with override

go

sp_configure 'Database Mail XPs',1

--go

--sp_configure 'SQL Mail XPs',0

go

reconfigure

go

Step 2
The Configuration Component Database account can be enabled by using the sysmail_add_account procedure. In this article, we are going create the account, "MyMailAccount," using mail.optonline.net as the mail server and
Cherukuri@test.com as the e-mail account.
Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'MyMailAccount',

@description = 'Mail account for Database Mail',

@email_address = 'cherukuri@test.com',

@display_name = 'MyAccount',

@username='cherukuri@test.com',

@password='abcd1234',
@mailserver_name = 'mail.test.com'

Step 3

The second sub component of the configuration requires us to create a Mail profile.
In this article, we are going to create "MyMailProfile" using the sysmail_add_profile procedure to create a Database Mail profile.
Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'MyMailProfile',

@description = 'Profile used for database mail'

Step 4
Now execute the sysmail_add_profileaccount procedure, to add the Database Mail account we created in step 2, to the Database Mail profile you created in step 3.
Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'MyMailProfile',

@account_name = 'MyMailAccount',

@sequence_number = 1

Step 5
Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.
Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name ='MyMailProfile', @principal_name = 'public',
@is_default = 1 ;

Step 6

Now let us send a test email from SQL Server.  

Please execute the statement below.

DECLARE @body1 VARCHAR(100)

set @body1 = 'Server : '+@@servername+ ' My Second Database Email '

EXEC msdb.dbo.sp_send_dbmail @recipients='paletiramaprasad@gmail.com',

@subject = 'My Database Mail Test',

@body = @body1,

@body_format = 'HTML' ;