In SQL Server, you can use the sysmail_help_account_sp stored procedure on the msdb database to retrieve a list of all Database Mail accounts.

You can also return account info based on its account name or ID.

Example

Here’s an example to demonstrate.

EXEC msdb.dbo.sysmail_help_account_sp;

Result (using vertical output):

account_id              | 1
name                    | DB Admin
description             | Mail account for admin emails.
email_address           | [email protected]
display_name            | DB Automated Mailer
replyto_address         | [email protected]
servertype              | SMTP
servername              | smtp.example.com
port                    | 25
username                | NULL
use_default_credentials | 0
enable_ssl              | 0

I’ve listed the results using vertical output so that you don’t have to scroll sideways to see all columns.

In my case, there’s just one Database Mail account.

Note that the sysmail_help_account_sp stored procedure is in the msdb database, and it’s owned by the dbo schema. Therefore, you’ll need to use the three part naming (like in my example) if msdb is not the current database.

Return Just One Account

Even though I only have one database mail account on my system, here’s an example of how to return a single account.

As mentioned, you can use the account name or its ID. Here’s an example of returning it by account ID:

EXEC msdb.dbo.sysmail_help_account_sp
    @account_id = 1;

You need to provide the account ID as an int.

And here’s an example of returning it by name:

EXEC msdb.dbo.sysmail_help_account_sp
    @account_name = 'DB Admin';

The account name is sysname.

Account Not Valid?

Providing an account ID that doesn’t exist returns the following error:

Msg 14606, Level 16, State 1, Procedure msdb.dbo.sysmail_verify_account_sp, Line 33
account id is not valid

Providing an account name that doesn’t exist returns the following error:

Msg 14607, Level 16, State 1, Procedure msdb.dbo.sysmail_verify_account_sp, Line 42
account name is not valid