[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL Server 2005 - Send E-Mail

Posted on 2009-04-27
Medium Priority
Last Modified: 2012-05-06
Help!!!!  Ok here is the problem.  I know enough about dbases to get myself in trouble.  I never used the dbase to send e-mails.  I've always developed an application to perform the e-mails.  I kind of like that because it is easier to troubleshoot for me.  Anyway, we had this one guy who worked with us but long story short he had to be let go.  He set up the e-mail in SQL server.  The last time the e-mail worked was before we changed the login info - we created a new account and password to the dbase.  I was able to figure out that that was part of the problem.  

So, went to System Databases | msdb | Security | Users and created the new account and mimicked what the old login.

I then went to Management | Database Mail | Configure Database Mail.  There I only had to modify the Manage profile security.  I went to Private Profile and selected the new log in and clicked on the Access check box.

So, if I run exec sp_sysmail_activate the e-mails are generated.  The problem is that they are suppose to be generated programatically.

He created a bunch of stored procedures in the dbase like sp_SendEventEmail.  I just can't find where they are called from.  In addition I am reciving the following error message:

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'.'
So, if I run the EXEC command in SQL server the e-mails will go out but I need it to work programatically again.  Any ideas?
Question by:CipherIS
  • 2
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24245165
got this from another forum

 Grant 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

Author Comment

ID: 24247102
Rats...  Didn't work.  Still same error.

Accepted Solution

CipherIS earned 0 total points
ID: 24649231
I figured it out

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question