How can I see the current Database Mail configuration?

Question

Our SQL Server (2008) instance is configured to send mail and everything is working correctly, but I can't figure out how to view the existing configuration, in particular the SMTP server.

From SSMS I can only start the configuration wizard, and I can't find anything online - plenty of info on how to set it up but nothing on how to view the current settings.

How can I view the existing settings?

Answer 1

If you open the Database Mail Configuration Wizard again, on the Select Configuration Task, the second option should be to Manage Database Mail accounts and profiles, select this option and click next. You should now be on the Manage Profile and Accounts step, select View, change, or delete an existing account and click next. You should be able to view the different accounts and their corresponding smtp server settings.

Answer 2

+1 to @jsauni for the wizard

You can also use the following system functions.

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

The wizard is probably easier to follow but these will give you everything at once without having to go back and forth through the wizard. I find it a bit cumbersome. :)

Answer 3

You can also query this data with a select statement:

select *
from msdb.dbo.sysmail_profile p 
join msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id 
join msdb.dbo.sysmail_account a on pa.account_id = a.account_id 
join msdb.dbo.sysmail_server s on a.account_id = s.account_id

If it is a migration that would require you to look this info up, I'd suggest the absurdly simple DBA Tools: Copy db Mail.