asked on 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
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
Microsoft SQL Server
Last Comment
Vitor Montalvão
Do you have a default "public" database mail profile
![Manage Profile Security]()
Take a look at this from BOL
Database Mail Profiles
Take a look at this from BOLDatabase Mail Profiles
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/
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/
Coloplast, do you still need help with this question?
ASKER
I've requested that this question be deleted for the following reason:
None of the answers could solve tour issue.
None of the answers could solve tour issue.
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.
ASKER
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
The mail profile is a public profile.
Do you have any more suggestions?
Thanks
Did you add the user to the DatabaseMailUserRole in msdb?
More information in this MSDN article.
More information in this MSDN article.
ASKER
Yes we did.
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?
Do you have any error information logged?
ASKER
I can't find any errors on the issue in any log.
The job completes successfully?
ASKER
Yes it does.
What about sysmail_event_log view? Can you see your email information there?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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