Create Database Mail Account And Profile.sql

--================================================================
-- DATABASE MAIL CONFIGURATION
--================================================================
/****
The script is broken down into the following sections:

    Creation of Database Mail Account
    Creation of Database Mail Profile
    Enable Database Mail for the SQL Server instance
    Returning list of accounts and profiles for the SQL Server instance
    Send test Database Mail
    Assignment of a Database Mail profile to the SQL Server Agent

****/
--==========================================================
-- Create a Database Mail account
--==========================================================
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = '<account_name, DBM account name, Database Mail Primary Account>',
    @description = '<description, , SQL Server Notification Service>',
    @email_address = '<email_address, email address for DBM. Does not need a valid mail account ,>',
    @replyto_address = '<replyto_address, reply email address for DBM. Does not need a valid mail account ,>',
    @display_name = '<display_name, friendly name for emails sent via DBM, Database Mail Account>',
    @mailserver_name = '<mailserver_name, smtp mail server name,>',
    @port = '<port_number, port number of the mailserver, 25>';


--==========================================================
-- Create a Database Mail Profile
--==========================================================
DECLARE @profile_id INT, @profile_description sysname;
SELECT @profile_id = COALESCE(MAX(profile_id),1) FROM msdb.dbo.sysmail_profile
SELECT @profile_description = 'Database Mail Profile for ' + @@servername 


EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = '<profile_name, DBM profile name, Database Mail Primary Profile>',
    @description = @profile_description;

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = '<profile_name, DBM profile name, Database Mail Primary Profile>',
    @account_name = '<account_name, DBM account name, Database Mail Primary Account>',
    @sequence_number = @profile_id;

-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = '<profile_name, DBM profile name, Database Mail Primary Profile>',
    @principal_id = 0,
    @is_default = 1 ;


--==========================================================
-- Enable Database Mail
--==========================================================
USE master;
GO

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO 


--EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N''
--EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
--GO

EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder = 0
GO


--==========================================================
-- Review Outcomes
--==========================================================
SELECT * FROM msdb.dbo.sysmail_profile;
SELECT * FROM msdb.dbo.sysmail_account;
GO


--==========================================================
-- Test Database Mail
--==========================================================
DECLARE @sub VARCHAR(100)
DECLARE @body_text NVARCHAR(MAX)
SELECT @sub = 'Test from New SQL install on ' + @@servername
SELECT @body_text = N'This is a test of Database Mail.' + CHAR(13) + CHAR(13) + 'SQL Server Version Info: ' + CAST(@@version AS VARCHAR(500))

EXEC msdb.dbo.[sp_send_dbmail] 
      @profile_name = '<profile_name, DBM profile name, Database Mail Primary Profile>'
	, @recipients = '<test_email_address, email address to send test email,>'
	, @subject = @sub
	, @body = @body_text


--================================================================
-- SQL Agent Properties Configuration
--================================================================
EXEC msdb.dbo.sp_set_sqlagent_properties 
	  @databasemail_profile = '<profile_name, DBM profile name, Database Mail Primary Profile>'
	, @use_databasemail=1
GO