It happens so that sometimes your mail server gets moved from one instance to another, one platform to another. Recently we have had to move our in house exchange mail server to Office365.
Here is a script to update the mail box and account details on an existing database mail account:
EXECUTE msdb.dbo.sysmail_update_account_sp
@account_name = 'SMTP_database_mail'
,@description = 'Mail account for administrative e-mail.'
,@email_address = 'dba@Adventure-Works.com'
,@display_name = 'Database Mail'
,@replyto_address = 'SQLAlerts@Adventure-Works.com'
,@mailserver_name = 'smtp.office365.com'
,@mailserver_type = 'SMTP'
,@port = 587
,@timeout = 60
,@username = 'SQL-Alerts@Adventure-Works.com'
,@password = 'AStrongPasswordGoesHere'
,@use_default_credentials = 0
,@enable_ssl = 1;
Now lets verify what we configured above by sending a test email.
use master
declare @servername varchar(100)
declare @testmsg varchar(100)
set @servername = replace(@@servername,'\','_')
set @testmsg = 'Test from ' + @servername
--send a test message.
exec msdb..sp_send_dbmail
@profile_name = 'DBA_Profile',
@recipients = 'SQL-Alerts@Adventure-Works.com',
@subject = @testmsg,
@body = @testmsg
Database Blog 