-- Enable service broker in the MSDB database.
USE [master]
GO
ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT
GO

--Enabling Database Mail
sp_configure 'show advanced options',1
reconfigure;
GO

-- Enable the db mail feature at server level
sp_configure 'Database Mail XPs',1
reconfigure;

--Creating a Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'SQLProfile',
@description = 'Mail Service for SQL Server' ;

-- Create a Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQL_Email_Account',
@email_address = 'somebody@gmail.com',
@mailserver_name = 'smtp.googlemail.com',
@port=465,
@enable_ssl=1,
@username='somebody@gmail.com',
@password='Emailid password'
-- TODO ENSURE VALID PASSWORD FOR THE ACCOUNT IS ENTERED ABOVE

-- Adding the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'SQLProfile',
@account_name = 'SQL_Email_Account',
@sequence_number =1 ;

-- Granting access to the profile to the DatabaseMailUserRole of MSDB
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'SQLProfile',
@principal_id = 0,
@is_default = 1;

--Sending Test Mail
-- See https://msdn.microsoft.com/en-us/library/ms190307.aspx for all arguments
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLProfile',
@recipients = 'somebodyelse@yahoo.co.uk',
@body = 'Database Mail Testing',
@subject = 'Database Mail from SQL Server';

select * from msdb.dbo.sysmail_event_log

USE [master]
GO

-- Create a Database Mail account 2
EXEC msdb.dbo.sysmail_add_account_sp
@account_name = 'SQL_Email_Account 2',
@description = 'Alternative Mail account',
@email_address = 'somebody3@gmx.co.uk',
@display_name = 'Alternate GMX',
@port=465,
@enable_ssl=1,
@username='somebody3@gmx.co.uk',
@password='ValidPassword',
@mailserver_name = 'mail.gmx.com';
GO

-- Add the account 2 to the profile
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'SQLProfile',
@account_name = 'SQL_Email_Account 2',
@sequence_number = 2 ;

-- Make one not the default
EXECUTE msdb.dbo.sysmail_update_principalprofile_sp
@profile_name = 'SQLProfile',
@principal_name = 'public',
@is_default = 0 ;

-- Show the new default profile
EXEC msdb.dbo.sysmail_help_principalprofile_sp

Use msdb
Go

select * from sysmail_profile
select * from sysmail_account
select * from sysmail_profileaccount where profile_id=1
select * from sysmail_server
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp @profile_name = 'SQLProfile';
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
EXEC msdb.dbo.sysmail_help_principalprofile_sp;
EXEC msdb.dbo.sysmail_help_status_sp;
exec [dbo].[sysmail_configure_sp] 'LoggingLevel', 3