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?
Best Answer
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).