Setting up a default/global mail account in database mail
Hi, Google. You kind of failed to help me out earlier when I was asking about "how to set a global mail profile for database mail in Microsoft SQL 2005." Here's what I wish you had said:
First of all, "Database mail" ("DBMail" or "Sysmail") is not the same as "SQL mail" ("SQLMail"). They're both stupid and overly complex, but DBMail is newer and slightly less stupid.
SQLMail uses an installed MAPI provider on your system to send mail, which means you need such a thing, possibly Outlook. DBMail apparently ignores your MAPI provider entirely. So if you find an article that says you need to install Outlook first, just ignore it; it's not true.
First, enable dbmail:
sp_configure 'Database Mail XPs', 1
Then, create a dbmail account and profile:
EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'TestAcct', @description = 'Mail account for use by all database users.', @email_address = 'email@example.com', @display_name = 'Test Server', @mailserver_name = 'smtp.example.com'
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'TestProf', @description = 'Profile used for administrative mail.'
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'TestProf', @account_name = 'TestAcct', @sequence_number = 1
Next, you can set that dbmail profile as the "default profile" ("global profile") for all users (ie. the "public" group):
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @principal_name = 'public', @profile_name = 'TestProf', @is_default = 1
And finally, try sending a test message:
EXECUTE msdb.dbo.sp_send_dbmail @firstname.lastname@example.org', @subject='test', @body='test'
And may I never have to look this up again.
ssh+2FA to all your machines, anywhere, without opening firewall ports.