What Can You Change With sysmail_update_account_sp?

Have you ever had an issue with SQL Server Database Mail settings being unexpectedly changed? If so, you have a few options when it comes to making corrections.

A quick fix might be to use the SSMS GUI to correct a change. A couple clicks might get you back to where you want to be.

If you need to make changes to multiple servers, you may want to avoid the GUI approach and all of the clicks that come with it. In that case, msdb contains a stored procedure called sysmail_update_account_sp that might be a more efficient approach. Let’s take a quick look at sysmail_update_account_sp and what it can do for you.

Plenty of Options

What possible changes can we make with sysmail_update_account_sp? Let’s go to the documentation over here.

Here are our options borrowed from the link above:

sysmail_update_account_sp [ [ @account_id = ] account_id ] [ , ] [ [ @account_name = ] 'account_name' ]
    , [ @email_address = ] 'email_address'
    , [ @display_name = ] 'display_name'
    , [ @replyto_address = ] 'replyto_address'
    , [ @description = ] 'description'
    , [ @mailserver_name = ] 'server_name'
    , [ @mailserver_type = ] 'server_type'
    , [ @port = ] port_number
    , [ @timeout = ] 'timeout'
    , [ @username = ] 'username'
    , [ @password = ] 'password'
    , [ @use_default_credentials = ] use_default_credentials
    , [ @enable_ssl = ] enable_ssl
[ ; ]

Keep in mind that it’s not an “all or nothing” scenario. If you only want to update one or a few settings out of the list above, that’s fine, but you do need to specify which account is being updated.

Let’s say I only need to change the display name that shows up when an e-mail is received from database mail. Let’s check what we have currently by querying msdb.dbo.sysmail_account:

SELECT * FROM msdb.dbo.sysmail_account;
GO

Let’s run our update for account_id 1 and change the display_name from “WrongWrongWrong” to “Callihan Data”:

EXEC sysmail_update_account_sp @account_id = 1
	,@display_name = 'Callihan Data';
GO

We’ll query msdb.dbo.sysmail_account again and confirm our change:

Got Mail

I had never had the need to use this procedure before. Recently, a unique situation came up that made this procedure very valuable compared to making a bunch of changes using the SSMS GUI. The GUI is fine for what it is, but sometimes it’s easier to save some clicks and use a stored procedure instead.

Thanks for reading!