Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

SQL Server 2008 R2 Standard x64

I can't send e-mail from this SQL Server (out of MANY that are configured pretty much identically AFAIK). I have created the mail profile and account. However, any time I tried to send an e-mail with sp_send_dbmail (with my account - in sysadmin role) or Database Mail->"Send Test E-Mail", nothing happens. Nothing goes into sysmail_log. The SQL Server error below is generated:

 The activated proc '[dbo].[sp_sysmail_activate]' running on queue 'msdb.dbo.ExternalMailQueue' output the following:  'The EXECUTE permission was denied on the object 'xp_sysmail_activate', database 'mssqlsystemresource', schema 'sys'.'

Details

  • msdb.dbo.sysmail_mailitems has a record for each mail
  • The database mail log is empty (msdb.dbo.sysmail_log)
  • msdb.dbo.sysmail_sentitems is empty
  • I have run sysmail_stop_sp and sysmail_start_sp
  • "is_broker_enabled" is set to "1" in msdb

How is the execute permission denied when I am part of the sysadmin role? Any suggestions on what could be happening?

share|improve this question
    
What's the exact version of the instance? –  Jon Seigel Mar 4 '13 at 18:59
    
For grins did you try GRANT EXEC ON schema::sys to [your_login_name];? –  Aaron Bertrand Mar 4 '13 at 19:02
    
Version 10.50.2500. Also, I tried GRANT EXEC ON schema::sys to [your_login_name]; No luck there. –  SomeGuy Mar 4 '13 at 19:10
add comment

1 Answer

up vote 1 down vote accepted

You aren't the one getting the error, the activated stored procedure is. That procedure is running under a different account than yours.

By default that procedure will run as what ever account is setup as the dbo of the msdb database, unless someone changed it of course. Best guess is that the account which is dbo of the msdb database isn't a member of the sysadmin fixed server role, or the activation principal of that queue has been changed (querying sys.service_queues will tell you this).

share|improve this answer
    
That was exactly the issue. I changed the owner of msdb to sa, and all the e-mails starting sending. Good catch and thanks! –  SomeGuy Mar 5 '13 at 16:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.