Background: Following in-step with my previous blog posts, you will know that I like to keep my SQL instances as free from clutter as possible.
I provide a script below that will return all database mail accounts not currently being used by any database mail profile on the current instance.
Having this list allows you to step through the Database Mail Configuration Wizard or by using the stored procedure msdb.dbo.sysmail_delete_account_sp to remove the unwanted account(s).
SELECT * FROM msdb.dbo.sysmail_account WHERE account_id not in ( SELECT account_id FROM msdb.dbo.sysmail_profileaccount )
Output: When run in your T-SQL editor of choice, this script will return a line for each database mail account that is not being used by any database mail profile on the current SQL Server instance.
Explanation: The inner-most SELECT returns all account id’s that are associated with profiles from the msdb.dbo.sysmail_profileaccount table. The outer SELECT then uses this list of existing and in-use accounts (matched to profiles) and returns everything from the msdb.dbo.sysmail_account table that is not being referenced.
At the time of this writing, I cannot seem to find anything on the Microsoft Docs site regarding the msdb.dbo.sysmail_account table or the msdb.dbo.sysmail_profileaccount table, so I have documented their columns below, as they appear in SQL Server 2019.
The msdb.dbo.sysmail_account table lists all database mail profiles configured on the SQL Server instance. The table contains the following columns:
account_id (PK, int, not null) name (sysname(nvarchar(128)), not null) description (nvarchar(256), null) email_address (nvarchar(128), not null) display_name (nvarchar(128), null) replyto_address (nvarchar(128), null) last_mod_datetime (datetime, not null) last_mod_user (sysname(nvarchar(128)), not null)
The msdb.dbo.sysmail_profileaccount table lists all database mail profiles along with all accounts associated with a particular profile. Remember, a profile can use more than one SMTP mail account. The table contains the following columns:
profile_id (PK, int, not null) account_id (PK, FK, int, not null) sequence_number (int, null) last_mod_datetime (datetime, not null) last_mod_user (sysname(nvarchar(128)), not null)
Removing the unused account(s): As mentioned earlier, you can step through the Database Mail Configuration Wizard, but to be honest, this is a very cumbersome UI to do even the simplest of tasks. Instead once you know the account id returned from the above query, running something similar to the following will do the same, only quicker. Obviously substitute in the preferred account id from your SQL Server instance to remove 🙂
More info on msdb.dbo.sysmail_delete_account_sp can be found here.
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_id = 2
Did you find this useful? Do you have a better way to achieve the desired result using T-SQL? Let me know in the comments below. Cheers.