How To Enable DB Mail in SQLEXPRESS 2008

By default SQL Server Express 2008 does not have DB mail enabled so you can't use the sp_send_dbmail stored procedure. You may have been getting the SQL Server blocked access to procedure 'dbo.sp_send_dbmail' error.

Quite a few articles I read, said that you can't use it - well - that's not strictly true! It's all there, it just has to be enabled :)

After I did this, SQLExpress was emailing just fine!

In Management Studio (SSMS), right click the server you want to enable DB mail on and choose Facets. In the View Facets pop-up choose the Surface Area Configuration option in the Facet dropdown. Then choose True for the DatabaseMailEnabled option.

Once you have clicked OK, you need to restart the SQL Express server (right click it and choose Restart).

The slightly more complicated part is that you now need to enter the data that other versions of SQL Server let you enter via the interface. So here goes:

1. Expand the tables > system tables in the msdb database
2. Create a mail account in sysmail_account
3. Create a profile in sysmail_profile
4. Match the new profile to the new account in sysmail_profileaccount (use the IDs from step 2 and 3)
5. Create a mail server for the new account  in sysmail_server (the server type will probably be SMTP, but check in sysmail_servertype if you are unsure)
6. Refresh the msdb DB, by right clicking the DB and choosing Refresh

Hey presto sp_send_dbmail now works :)