February 20, 2007 at 2:17 pm
DB mail has been running fine until recently. Sending a test e-mail produces no errors, but the mail is never received. The mail log shows no errors. The mail queue shows e-mail(s) with a state of NOTIFY. I have also noticed that when I try to send mail, DatabaseMail90.exe is not executing.
However, when I execute the following mail is sent and DatabaseMail90.exe runs.
exec sp_sysmail_activate
Sending a test e-mail creates the following entry in the Windows application log.
The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'EXECUTE permission denied on object 'xp_sysmail_activate', database 'mssqlsystemresource', schema 'sys'.'
The user shows to be me and I have admin rights on the machine. I also have several other instances running on this box and this is the only instance having a problem.
Thanks.
February 23, 2007 at 8:00 am
This was removed by the editor as SPAM
September 6, 2007 at 8:08 am
I received the same error message too. I could not find any information on this stored procedure.
January 4, 2008 at 7:16 am
I had this similar issue but found the solution (at least for me anyhow). For an install of Virtual Center I temporarily had to make another account the dbo on the msdb database. After the install I forgot to set it back to the way it was. Apparently this misconfiguration was what was hosing up my database mail and the account that I was trying to fire off the stored procedure with didn't have the permission to do so while the VC account was dbo. Make sure whatever account you're trying to use to send db mail with has permission to execute stored procedures from msdb and you should be fine.
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
January 6, 2008 at 7:44 pm
The first question to ask is "what's changed?". Things don't just stop working.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
June 19, 2008 at 7:58 am
Its very old issue but still replying because some one like me need not re invent wheel..... 🙂
I was able to resolve this by granting execute permission to the account that is running the SQL agent account and the mail...
use master
GRANT EXECUTE ON master.dbo.xp_sysmail_activate TO [mycomp\test]
use msdb
alter queue ExternalMailQueue with status = on
June 25, 2008 at 6:56 am
Yesterday and today i have same problem.
I can right click on the job and run this and the email fires off.
I do see a lot of activity threads of DatabaseMail90 -id in here.
I see all the events in SELECT * FROM sysmail_allitems with status of not sent...........
What is the port it uses and I know its not using the SMTP anymore in SQL 2005 ...how to track further.
If i run this EXECUTE dbo.sysmail_start_sp the status in the
select * from sysmail_allitems is set to SENT and the email fires off.
Not sure why but how to not keep having to run this command
June 25, 2008 at 2:27 pm
Do you see any permissions issue in the SQL log? can you paste the complete error msg? you might want to try grating exec permissions to the account running SQL agent services...
June 25, 2008 at 4:47 pm
restart agent.
June 25, 2008 at 7:04 pm
Restarted the agent seems to be working great now thanks........did not think of the obvious one to start.
July 24, 2008 at 3:58 pm
Thank you sir, it worked for me,
September 3, 2015 at 5:26 pm
Hello,
I have received the same error like below.
The EXECUTE permission was denied on the object 'xp_sysmail_activate', database 'mssqlsystemresource', schema 'sys'.'
I have resolved it on my machine, please check the below things.
Please check the owner of dbo in msdb database.
Verify if the login mapped to dbo user is a member of sysadmin fixed server.
If not add the login to sysadmin role and try sending the test mail again.
Check for any error in the application log and view database mail log.
Thanks,
Pramodkumar.
September 3, 2015 at 5:54 pm
Hello,
I have received the same error like below.
The EXECUTE permission was denied on the object 'xp_sysmail_activate', database 'mssqlsystemresource', schema 'sys'.'
I have resolved it on my machine, please check the below things.
Please check the owner of dbo in msdb database.
Verify if the login mapped to dbo user is a member of sysadmin fixed server.
If not add the login to sysadmin role and try sending the test mail again.
Check for any error in the application log and view database mail log.
Thanks,
Pramodkumar.
September 3, 2015 at 10:15 pm
pramodkumar.singh 17457 (9/3/2015)
Hello,I have received the same error like below.
The EXECUTE permission was denied on the object 'xp_sysmail_activate', database 'mssqlsystemresource', schema 'sys'.'
I have resolved it on my machine, please check the below things.
Please check the owner of dbo in msdb database.
Verify if the login mapped to dbo user is a member of sysadmin fixed server.
If not add the login to sysadmin role and try sending the test mail again.
Check for any error in the application log and view database mail log.
Thanks,
Pramodkumar.
7 year old thread.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply