I'm having a issue with my database mail not showing my new profile I created. I'm executing this:
Execute msdb.dbo.sysmail_add_account_sp
@account_name = 'email here',
@email_address = 'email here' ,
@display_name = 'White Box Gaming' ,
@replyto_address = 'email here' ,
@description = 'Profile used to send mail' ,
@mailserver_name = 'smtp.gmail.com',
@mailserver_type = 'SMTP',
@port = 587,
@username = 'email here',
@password = 'password',
@enable_ssl = 0
No errors but when I try to view it:
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
New profile does not appear. What did I miss?
After creating the mail account, assign it to a mail profile (the query joins sysmail_profile with sysmail_profileaccount)
--get profiles
EXEC msdb.dbo.sysmail_help_profile_sp;
--if there is no profile, create one
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name = N'my email profile', @description = N'email profile description';
--get accounts (note down the account_id)
EXEC msdb.dbo.sysmail_help_account_sp;
--get profiles (note down the profile_id)
EXEC msdb.dbo.sysmail_help_profile_sp;
--add account to profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_id= 1/*profile_id goes here*/, @account_id = 1 /*account_id goes here*/, @sequence_number=1;
--list accounts for each profile
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;
--or
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;