SQL Server 2005 - Send E-Mail

Posted on 2009-04-27
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
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    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
    LVL 1

    Author Comment

    Rats...  Didn't work.  Still same error.
    LVL 1

    Accepted Solution

    I figured it out

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now