42

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?

26

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.

  • 1
    Thanks! I knew it had to be simple but too often wizards will start changing stuff before you know it :) – Alex Jul 26 '13 at 10:57
52

+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. :)

  • 3
    +1 sysmail_help_account_sp includes the server details I was after – Alex Jul 26 '13 at 12:31
12

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.