SQL Server Database Mail setup

Firstly we need to enable Database Mail feature in the server that has MS SQL Server installed. This can be done using GUI or TSQL. In this tutorial I will be demonstrating using TSQL, as I prefer it.

Step 01:
Enable Database Mail feature in MS SQL.

USE master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go

Step 02:
Create database account.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Admin',
@description = 'Mail account for Database Mail',
@email_address = 'me@example.com',
@display_name = 'Younten Jamtsho',
@username='me@example.com',
@password='meeeeeeeeeeee',
@mailserver_name = 'mail.example.com'

Step 03:
Create a mail profile.

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'AdminProfile',
@description = 'Profile used for database mail'

Step 04:
Add the account to the profile.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'AdminProfile',
@account_name = 'Admin',
@sequence_number = 1

Step 05:
Grant access to the profile.

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

Now that you have configured the Database Mail. Lets sending mail though it.

EXEC msdb.dbo.sp_send_dbmail @recipients='you@example',
@subject = 'Database Mail',
@body = 'Testing mail system of SQL Server',
@profile_name = 'AdminProfile'


If in any case you run into some problem and you need to delete all the configurations that you have just done. Here is how to do it.

EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = 'AdminProfile',
@account_name = 'Admin'

EXECUTE msdb.dbo.sysmail_delete_profile_sp
@profile_name = 'AdminProfile'

EXECUTE msdb.dbo.sysmail_delete_account_sp
@account_name = 'Admin'

Here another way you can delete any Database Mail account and profile in your SQL Server.

Declare @ProfileName nvarchar(20), @AccountName nvarchar(20)
IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profileaccount pa
JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE
p.name = @ProfileName AND
a.name = @AccountName)
BEGIN
PRINT 'Deleting Profile Account'
EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName
END

IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profile p
WHERE p.name = @ProfileName)
BEGIN
PRINT 'Deleting Profile.'
EXECUTE msdb.dbo.sysmail_delete_profile_sp
@profile_name = @ProfileName
END

IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_account a
WHERE a.name = @AccountName)
BEGIN
PRINT 'Deleting Account.'
EXECUTE msdb.dbo.sysmail_delete_account_sp
@account_name = @AccountName
END

Here are the list of tables associated with Database Mail in SQL Server.

--Profiles
SELECT * FROM msdb.dbo.sysmail_profile

--Accounts
SELECT * FROM msdb.dbo.sysmail_account

--Profile Accounts
select * from msdb.dbo.sysmail_profileaccount

--Principal Profile
select * from msdb.dbo.sysmail_principalprofile

--Mail Server
SELECT * FROM msdb.dbo.sysmail_server
SELECT * FROM msdb.dbo.sysmail_servertype
SELECT * FROM msdb.dbo.sysmail_configuration

--Email Sent Status
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_unsentitems
SELECT * FROM msdb.dbo.sysmail_faileditems

--Email Status
SELECT SUBSTRING(fail.subject,1,25) AS 'Subject',
fail.mailitem_id,
LOG.description
FROM msdb.dbo.sysmail_event_log LOG
join msdb.dbo.sysmail_faileditems fail
ON fail.mailitem_id = LOG.mailitem_id
WHERE event_type = 'error'

--Mail Queues
EXEC msdb.dbo.sysmail_help_queue_sp

--DB Mail Status
EXEC msdb.dbo.sysmail_help_status_sp