In SQL Server, we can setup multiple email configurations out of which only some were set to default.

I have tried using the following query to get the list of accounts:

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

Is there any way to identify the default mail configuration profile?


Add a join to msdb.dbo.sysmail_principalprofile and check the is_default column to identify the default profile:

FROM msdb.dbo.sysmail_profile p 
JOIN msdb.dbo.sysmail_principalprofile pp ON pp.profile_id = p.profile_id AND pp.is_default = 1
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;

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.