SQL DBMail not working

Coloplast
Coloplast used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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 Administrator

Commented:
Do you have a default "public" database mail profile
Manage Profile SecurityTake a look at this from BOL
Database Mail Profiles
lcohanDatabase Analyst

Commented:
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/
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Coloplast, do you still need help with this question?
ColoplastIT engineer

Author

Commented:
I've requested that this question be deleted for the following reason:

None of the answers could solve tour issue.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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.
ColoplastIT engineer

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
Did you add the user to the DatabaseMailUserRole in msdb?
More information in this MSDN article.
ColoplastIT engineer

Author

Commented:
Yes we did.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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?
ColoplastIT engineer

Author

Commented:
I can't find any errors on the issue in any log.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
The job completes successfully?
ColoplastIT engineer

Author

Commented:
Yes it does.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
What about sysmail_event_log view? Can you see your email information there?
IT Engineer
Distinguished Expert 2017
Commented:
And there are more from where you can do some troubleshooting. Check this MSDN article.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial