{"id":765,"date":"2022-08-09T20:01:59","date_gmt":"2022-08-10T01:01:59","guid":{"rendered":"https:\/\/brentec.ca\/blog\/?p=765"},"modified":"2022-08-09T20:01:59","modified_gmt":"2022-08-10T01:01:59","slug":"script-to-identify-unused-sql-server-database-mail-accounts","status":"publish","type":"post","link":"https:\/\/brentec.ca\/blog\/2022\/08\/script-to-identify-unused-sql-server-database-mail-accounts\/","title":{"rendered":"Script to identify unused SQL Server database mail accounts"},"content":{"rendered":"

Background<\/strong><\/span>: 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.
\nI provide a script below that will return all database mail accounts not currently being used by any database mail profile on the current instance.
\nHaving this list allows you to step through the Database Mail Configuration Wizard<\/a> or by using the stored procedure msdb.dbo.sysmail_delete_account_sp<\/a> to remove the unwanted account(s).<\/p>\n

\r\nSELECT *\r\nFROM msdb.dbo.sysmail_account\r\nWHERE account_id not in\r\n  (\r\n  SELECT account_id\r\n  FROM msdb.dbo.sysmail_profileaccount\r\n  )\r\n<\/pre>\n
Output<\/strong><\/span>: 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.<\/p>\n
Explanation<\/strong><\/span>: The inner-most SELECT<\/em> returns all account id’s that are associated with profiles from the msdb.dbo.sysmail_profileaccount<\/em> table. The outer SELECT<\/em> then uses this list of existing and in-use accounts (matched to profiles) and returns everything from the msdb.dbo.sysmail_account<\/em> table that is not being referenced.<\/p>\n
At the time of this writing, I cannot seem to find anything on the Microsoft Docs site regarding the msdb.dbo.sysmail_account<\/em>\u00a0table or the\u00a0 msdb.dbo.sysmail_profileaccount<\/em> table, so I have documented their columns below, as they appear in SQL Server 2019.<\/p>\n
The msdb.dbo.sysmail_account<\/em> table lists all database mail profiles configured on the SQL Server instance. The table contains the following columns:<\/p>\n
account_id (PK, int, not null)\r\nname (sysname(nvarchar(128)), not null)\r\ndescription (nvarchar(256), null)\r\nemail_address (nvarchar(128), not null)\r\ndisplay_name (nvarchar(128), null)\r\nreplyto_address (nvarchar(128), null)\r\nlast_mod_datetime (datetime, not null)\r\nlast_mod_user (sysname(nvarchar(128)), not null)<\/pre>\n
The msdb.dbo.sysmail_profileaccount<\/em> 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:<\/p>\n
profile_id (PK, int, not null)\r\naccount_id (PK, FK, int, not null)\r\nsequence_number (int, null)\r\nlast_mod_datetime (datetime, not null)\r\nlast_mod_user (sysname(nvarchar(128)), not null)<\/pre>\n
Removing the unused account(s)<\/strong><\/span>: 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 \ud83d\ude42
\nMore info on msdb.dbo.sysmail_delete_account_sp<\/em> can be found\u00a0here<\/a>.<\/p>\n
\r\nEXECUTE msdb.dbo.sysmail_delete_account_sp @account_id = 2\r\n<\/pre>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 …<\/p>\n
  Script to identify unused SQL Server database mail accounts<\/span> Read More »<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"categories":[58,3,45],"tags":[],"_links":{"self":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/765"}],"collection":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/comments?post=765"}],"version-history":[{"count":35,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/765\/revisions"}],"predecessor-version":[{"id":812,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/765\/revisions\/812"}],"wp:attachment":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/media?parent=765"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/categories?post=765"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/tags?post=765"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}