CipherIS
asked on
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:
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?
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:
Message
The activated proc [dbo].[sp_sysmail_activate
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?
ASKER
Rats... Didn't work. Still same error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_acti
use msdb
alter queue ExternalMailQueue with status = on