Database Mail Info

Database Mail profile information, along with account names and SMTP server


/*
Compatibility list:
MSSQL2005 - MSSQL2008 - MSSQL2008R2 - MSSQL2012
 
Does not work
MSSQL2000
 
Update Log:
*/

DECLARE       @DBEngineLogin       VARCHAR(100)
EXECUTE       master.dbo.xp_instance_regread
              @rootkey      = N'HKEY_LOCAL_MACHINE',
             @key          = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
              @value_name   = N'ObjectName',
              @value        = @DBEngineLogin OUTPUT

select 
p.name
, p.description
,a.name
, a.description
, a.email_address
, a.display_name
, a.replyto_address
, s.servertype
, s.servername
, s.port
, coalesce(s.username,@DBEngineLogin) -- If the username is empty, it is authenticating using Windows Credentials
, s.use_default_credentials
, s.enable_ssl
from msdb.dbo.sysmail_profile p
inner join msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id
inner join msdb.dbo.sysmail_account a on a.account_id = pa.account_id
inner join msdb.dbo.sysmail_Server s on a.account_id = s.account_id
GO

Leave a Reply

Your email address will not be published. Required fields are marked *