Changing the information for a database mail account

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: