• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 729
  • Last Modified:

SQL Server 2005 - Send E-Mail

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?
  • 2
1 Solution
Aneesh RetnakaranDatabase AdministratorCommented:
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
CipherISAuthor Commented:
Rats...  Didn't work.  Still same error.
CipherISAuthor Commented:
I figured it out
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now