The wonderful thing about I'm the only one
Everything here is my opinion. I do not speak for your employer.
February 2009
March 2009

2009-02-05 »

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 = '',
    @display_name = 'Test Server',
    @mailserver_name = ''

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

And may I never have to look this up again.

I'm CEO at Tailscale, where we make network problems disappear.

Why would you follow me on twitter? Use RSS.

apenwarr on