0

I've been trying for hours to get Database Mail working on SQL Server 2008 R2 and have reached what seems like a dead end.

I have completed the Database Mail Configuration tool, made sure that SQL Brower is turned on and followed all the steps in the Troubleshooting Database Mail Books Online.

When I run SELECT * FROM sysmail_event_log, all I get in the description column is

Mail configuration information could not be read from the database.

I've tried Googling this, but most of the matches are from people who have the luxury of an exception stack. I don't get any exception to work with.

Any clues?

Michael Rodrigues
  • 5,057
  • 3
  • 25
  • 51

2 Answers2

1

After playing around for far too long I had the inspiration to run SQL Profiler, capturing all errors (don't know why I didn't think of that earlier!).

During the Send Test Mail process it revealed that

The EXECUTE permission was denied on the object 'sysmail_help_configure_sp', database 'msdb', schema 'dbo'. against System\NT AUTHORITY

So I added a bunch of permissions to that user, and suddenly everything works!

My NT AUTHORITY user now has following permissions in the msdb database:

  • Member of the DatabaseMailUserRole

EXECUTE permission for the following:

  • dbo.sp_ProcessResponse
  • dbo.sp_readrequest
  • dbo.sysmail_configure_sp
  • dbo.sysmail_help_account_sp
  • dbo.sysmail_help_admin_account_sp
  • dbo.sysmail_help_configure_sp
  • dbo.sysmail_help_configure_value_sp
  • dbo.sysmail_help_principalprofile_sp
  • dbo.sysmail_help_profile_sp
  • dbo.sysmail_help_profileaccount_sp
  • dbo.sysmail_help_profileaccount_sp
  • dbo.sysmail_help_profileaccount_sp
  • dbo.sysmail_help_profileaccount_sp
Michael Rodrigues
  • 5,057
  • 3
  • 25
  • 51
  • If you have nothing of consequence (apart from your new Database Mail) setup in your msdb database you could recreate it from scratch. – Ian Yates Nov 30 '12 at 05:37
0

Refer the below link :

http://www.practicalsqldba.com/2012/03/sql-server-2005-database-mail-stopped.html?showComment=1463171213768#c7726740071461349854

Updating the stored procedure sysmail_help_admin_account_sp in msdb resolved the issue.

Ajay2707
  • 5,690
  • 6
  • 40
  • 58
Ramkumar Sambandam
  • 487
  • 1
  • 6
  • 11
  • 2
    Don't just rely on the OP visiting the link. One day the link will be bad. Add more precise instructions in your answer please! – Adam Erstelle May 13 '16 at 20:37