/* Turn on MsSQL emailing on Created By: Steve Ling 2024/08/31 */ -- Run First if (SELECT [value] FROM sys.configurations WHERE [name] = N'show advanced options') <> 1 Begin EXEC sp_configure 'show advanced options', 1; RECONFIGURE; End if (SELECT [value] FROM sys.configurations WHERE [name] = N'Database Mail XPs') <> 1 Begin EXEC sp_configure 'Database Mail XPs', 1; RECONFIGURE End -- Declaration Parameters Declare @SMTP nvarchar(50), @SendFromEmail nvarchar(100), @Port int, @SSL int, @User nvarchar(50), @Pass nvarchar(50) -- SMTP server Set @SMTP = 'mail.onling.com' Set @SendFromEmail = 'no_reply@sflservicesllc.com' Set @Port = 25 Set @SSL = 0 --1 = True, 0= False --If @SSL is false leave the following blank Set @User = '' Set @Pass = '' -- ########### No changes below here ########### -- Create a Database Mail profile EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'Notifications', @description = 'Profile used for sending outgoing notifications.' ; -- Grant access to the profile to the DBMailUsers role EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'Notifications', @principal_name = 'public', @is_default = 1 ; -- Create a Database Mail account EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'SMTP', @description = 'Mail account for sending outgoing notifications.', @email_address = @SendFromEmail, @display_name = 'Automated Mailer', @mailserver_name = @SMTP, @port = @Port, @enable_ssl = @SSL, @username = @User, @password = @Pass ; -- Add the account to the profile EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'Notifications', @account_name = 'SMTP', @sequence_number =1 ; GO -- Run to Delete /* EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'Notifications' EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = 'Notifications' EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'SMTP' EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'Notifications' */ -- Run to Test /* EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Notifications', @recipients = 'Use a valid e-mail address', @body = 'The database mail configuration was completed successfully.', @subject = 'Automated Success Message'; GO */