Problem
We all know about SQL Server Database Mail which is very feature rich to get mail alerts from our SQL Servers. Database Mail can be configured by creating mail accounts where we configure the required details like the SMTP server name, port number, email address, etc. which is used to send mail notifications. Sometimes there is a need to update these details and in this tip I will guide you on how to change Database Mail settings.
Solution
SQL Server Database Mail can be used to send a text message, query results, file attachment or just a plain email. It can also be used to notify users or administrators regarding events raised in SQL Server. For example, if any SQL Server Agent Job fails or there are latency problems for replication then SQL Server can use this feature to notify the owners or database administrators.
To use database mail, first we need to setup a mail account with SMTP details and then we will use that account for notifications. Read these tips for more information.
Suppose you already have configured Database Mail and there is a requirement to change a few details for the mail account or profile, like changing the SMTP server name. I will explain how to make these changes below.
Change SQL Server Database Mail Account using SSMS Wizard
Step 1: Launch SQL Server Management Studio and connect to the SQL Server instance where you want to change the details of a mail account. Expand the Management folder, go to Database Mail and right click, then select “Configure Database Mail”. A list of options will appear as shown in the below screenshot.
Step 2: Click on the “Configure Database Mail” option, a separate window will appear named “Welcome to Database Mail Configuration Wizard” as shown below.
Step 3: Click on the Next button and another window will open with the name “Select Configuration Task” as shown in the below screenshot. You will get multiple options here. We can create a new mail profile, new mail accounts, alter existing profiles and accounts, and manage security of the database mail by selecting the appropriate options on this page.
Our goal is to change the SMTP server, so we are going to choose the second option which is “Manage Database Mail accounts and profiles”. Choose this option and click the Next button to proceed.
Step 4: Once you click on Next, another window named “Manage Profiles and Accounts” will open. We can see four options. Since we want to change an existing account we will choose the second option “View, change or delete an existing account” then click the Next button to proceed.
Step 5: On the “Manage Existing Account” window we can update any information or details related to a particular account. Choose your account name from the drop down list that you want to update. You will see SMTP details appear below the account name after the selection of the account. Here we can see the existing details of this account such as email, server name, port number, etc.
On this screen, make the necessary changes and click on the Next button.
Step 6: On the next window “Complete the Wizard” it will show us the changes we are going to make. To save the changes click on the Finish button.
Step 7: At the very end, the “Configuring…” page will show you the status of this change. It will show “Success” if your changes are successful. Now click on close button to close the wizard. Restart the SQL Server Agent service and you are done with your changes. Go ahead and check whether mail is sending notifications using the changed information.
Change SQL Server Database Mail Account using T-SQL
Step 1: As you may know, all Database Mail details are saved in the msdb system database. So first we will check the SMTP details for the database mail accounts using Database Mail system tables. Run the below code to get details for all mail accounts on that particular SQL Server instance.
SELECT [sysmail_server].[account_id]
,[sysmail_account].[name] AS [Account Name]
,[servertype]
,[servername] AS [SMTP Server Address]
,[Port]
FROM [msdb].[dbo].[sysmail_server]
INNER JOIN [msdb].[dbo].[sysmail_account]
ON [sysmail_server].[account_id]=[sysmail_account].[account_id]
We can see the SMTP details of a mail account present on this SQL Server instance in the below screenshot. Now we have to change details of this mail account.
Step 2: There is a database mail system stored procedure known as sysmail_update_account_sp. This stored procedure changes the information in an existing Database Mail account. When both the account name and the account id are specified, the stored procedure changes the account name in addition to updating the information for the account. We will use this stored procedure to change the details of the existing account. Run the below code to make the updates.
-- Run below SP to change any info of mail account. Replace XXXX & XX with your correct SMTP IP address and port no.
EXECUTE msdb.dbo.sysmail_update_account_sp
@account_name = 'MSSQL_Name_mail_account'
,@description = 'Mail account for administrative e-mail.'
,@mailserver_name = 'smtp.XXXX.com'
,@mailserver_type = 'SMTP'
,@port = XX5
Step 3: As we have changed the SMTP details in the above step, we can validate whether details are updated or not. Run the same code from Step 1. Restart SQL Server Agent and test the mail notification to see whether is working or not.
Next Steps
- Make sure to test this solution in lower life cycle before replicating it directly to production servers.
- Explore more knowledge on SQL Server Database Mail and SQL Server Database Administration Tips.
