Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Modify SQL Server Database Mail Accounts


By:   |   Read Comments   |   Related Tips: More > Database Mail

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.

launch DBMail configuration Page

Step 2: Click on the "Configure Database Mail" option, a separate window will appear named "Welcome to Database Mail Configuration Wizard" as shown below.

Welcome to Database Mail Configuration Wizard

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.

configuration_task_page

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.

manage_profiles_accounts Page

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.

manage_existing_account Page

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.

Complete the wizard page

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.

Configuring page with success status

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.

8_check_the_SMTPDetails using T-SQL

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


9_SP_change using T-SQL

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.

Verified SMTP changes
Next Steps


Last Update:






About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools