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?