SQL DBMail not working

Hi Guys,
I have a Stored Procedure containing code that will send an email.
This works if I execute the stored procedure directly.
When I start the Stored Procedure from a SQL job the mail never arrives, actually it's never being sent.
I tried to make the account running the job SysAdmin and then it works, but of course this is not to be :-)
So it's a security issue, but which rights do I need to give the user runnning the job?
The MailUser Role in MSDB is not enough!
I'm running SQL 2008R2 on this system.
Thanks
ColoplastAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jyothees venkatCommented:
Let's find out what the issue is. Try these steps first.

1.Right click on "SQL Server Agent"
2.Then "Properties"
3.Then go to "Alert System" section
4.Tick the "Enable mail profile" box
5.Then "Ok"
6.Restart the SQL Server Agent service
Jose TorresCertified Database AdministratorCommented:
Do you have a default "public" database mail profile
Manage Profile SecurityTake a look at this from BOL
Database Mail Profiles
lcohanDatabase AnalystCommented:
Your options are to use an existing SA account to run the SP as that account instead of the SQL Agent startup account as indeed  - that is a no-no from security point of view. Just put a command like this in the SQL job step and make sure that login has the sufficient rights:

 EXECUTE AS user = 'special_user'

EXECUTE YourProcerdure

REVERT

Alternatively you could create and use a Proxy account to run that SQL Job step

https://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx
http://www.sql-server-performance.com/2010/sql-server-job-proxy-account/
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Coloplast, do you still need help with this question?
ColoplastAuthor Commented:
I've requested that this question be deleted for the following reason:

None of the answers could solve tour issue.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Coloplast, you had 3 comments for your question and you didn't give any feedback to them. You should give the opportunity for Experts help you better.
ColoplastAuthor Commented:
Sorry guys, I tried all of your suggestions but nothing is solving the issue.
The mail profile is a public profile.
Do you have any more suggestions?
Thanks
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you add the user to the DatabaseMailUserRole in msdb?
More information in this MSDN article.
ColoplastAuthor Commented:
Yes we did.
Vitor MontalvãoMSSQL Senior EngineerCommented:
May the problem not be with the send mail but with lack of permissions to perform the other actions in the stored procedure?
Do you have any error information logged?
ColoplastAuthor Commented:
I can't find any errors on the issue in any log.
Vitor MontalvãoMSSQL Senior EngineerCommented:
The job completes successfully?
ColoplastAuthor Commented:
Yes it does.
Vitor MontalvãoMSSQL Senior EngineerCommented:
What about sysmail_event_log view? Can you see your email information there?
Vitor MontalvãoMSSQL Senior EngineerCommented:
And there are more from where you can do some troubleshooting. Check this MSDN article.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.