February 8, 2010 at 8:30 am
Hi folks,
I have a stored procedure to send out emails, I have no problem running it and I do receive emails.
The problem is when I schedule it in a job, I don't receive any email, the job itself succeed, no error message.
To double check, I also checked the McAfee setting, nothing was blocked at that time.
Can anyone tell me where to check now? Any hint would be appreciated.
Thanks.
February 8, 2010 at 8:54 am
If this is an email being sent as part of the alert, you should make sure that the Mail session information is configured correctly by going into the properties of the SQL Server Agent, selecting “Alert System” and verifying the settings. If it is simply sending mail which is not part of any alert, make sure that the account under which the SQL Server Agent service is running is a member of the DatabaseMailUserRole in msdb. Hope this helps.
February 8, 2010 at 9:14 am
DavidZahner (2/8/2010)
If it is simply sending mail which is not part of any alert, make sure that the account under which the SQL Server Agent service is running is a member of the DatabaseMailUserRole in msdb. Hope this helps.
Thank you for your hint, this is actually a very simple job: the sp sends email, it works if I run it explicitly. Then I schedule it in a job, run as me, it went through, just no email would be sent out. I checked the DatabaseMailUserRole, I am in the list and I am the only one record.
I changed the job to run as sa, it went through as normal, no email was sent out.
February 8, 2010 at 9:27 am
To get some more detail, you can query msdb.dbo.sysmail_mailitems to see what the result was and to get the send_request_user to see what authentication is being used when called, etc.
February 8, 2010 at 9:55 am
DavidZahner (2/8/2010)
To get some more detail, you can query msdb.dbo.sysmail_mailitems to see what the result was and to get the send_request_user to see what authentication is being used when called, etc.
Hi David,
Thank you very much for the hint, I didn't know that all emails were actually retained in the system table.
I checked my system table msdb.dbo.sysmail_mailitems, I don't see all of the emails that are supposed to be sent out via the sp, I do see the one that is sent out as notification to operator after the job finished. Some interesting finding here: when I run the sp directly, sysmail_mailitems shows the request user is me, when I run the job, it shows request user is NT AUTHORITY\SYSTEM, so apparently this is the problem, NT AUTHORITY\SYSTEM is not in the authorized emailing user list.
But I already set the job be executed as sa, why it still shows under NT AUTHORITY\SYSTEM? other than "owner" setting, is there a "Run as" setting for job?
Thank you again for your time.
February 8, 2010 at 10:18 am
I did more test: I added 'NT AUTHORITY\SYSTEM' as member of DatabaseMailUserRole, I would assume this allows NT AUTHORITY\SYSTEM' to send out email, (although this is not professional and might have security issue, but just for a test purpose)
Then I run the job again, I still don't see any email went through except for the email notifying the job succeeded.
And then I modified my sp to
ALTER proc [dbo].[spEmailNotificationForPAA] with EXECUTE AS owner
Now this time even the notification email is not sent out, not to say all the others bunch emails.
February 8, 2010 at 10:19 am
Cool, at least it is logging the issue. I would guess that the job step/ proxy is not configured correctly so the job step is defaulting to the security under which the SQL Server Agent service is running (Local System in this case). You can add [NT AUTHORITY\SYSTEM] to the DatabaseMailUserRole in msdb or change the service to run under a different explicitly defined windows account and add that to the role in msdb (this would be the best route to take).
February 8, 2010 at 10:22 am
If you are using Execute As and are accessing resources outside of the current database (all mail items in this case) you will need to set the trustworthy setting for the database in which the proc is being run (Alter Database <DBName> Set Trustworthy On).
February 8, 2010 at 10:28 am
DavidZahner (2/8/2010)
Cool, at least it is logging the issue. I would guess that the job step/ proxy is not configured correctly so the job step is defaulting to the security under which the SQL Server Agent service is running (Local System in this case). You can add [NT AUTHORITY\SYSTEM] to the DatabaseMailUserRole in msdb or change the service to run under a different explicitly defined windows account and add that to the role in msdb (this would be the best route to take).
Ahhh you must be replying too fast, in my previous email I said adding [NT AUTHORITY\SYSTEM] to DatabaseMailUserRole doesn't help and setting the job's owner as 'sa' is no help as well.
February 8, 2010 at 10:30 am
DavidZahner (2/8/2010)
If you are using Execute As and are accessing resources outside of the current database (all mail items in this case) you will need to set the trustworthy setting for the database in which the proc is being run (Alter Database <DBName> Set Trustworthy On).
Like i said in the previous post, Execute As simply doesn't apply to this case (no external resource is required) and even bring things worse (no any email would be sent out)
February 8, 2010 at 10:38 am
There is a Run As setting for the job step, but that would just muddy the authentication waters here. I would recommend creating a Windows account for the SQL Server Agent service and add that to the appropriate database and server roles within SQL including the DatabaseMailUserRole in msdb. This will allow you to adjust the permissions for this account without giving undue permissions to the built-in system accounts.
February 8, 2010 at 12:46 pm
DavidZahner (2/8/2010)
There is a Run As setting for the job step, but that would just muddy the authentication waters here. I would recommend creating a Windows account for the SQL Server Agent service and add that to the appropriate database and server roles within SQL including the DatabaseMailUserRole in msdb. This will allow you to adjust the permissions for this account without giving undue permissions to the built-in system accounts.
I didn't create the account because for a test even if I set sa as the owner of the job, the email won't be sent out, so there is no reason to create an extra account for SQL server agent.
I think the problem is not the job permission, it's something about the msdb.dbo.sp_send_dbmail used in sending email in the sp. It works when I execute in query windows (that means the sp is executed under mine name, which is dbo here), but when the sp is executed in a job, and when I run the job, it is somehow not under me but some other account --- this is my guess.
And I suspect SQL won't throw any error when there is permission insufficiency to run the msdb.dbo.sp_send_dbmail, it just silently quit.
The problem I found is: I did some test here, I used a non-existing database email profile in the sp, and have a try/catch to write any exception to an error table, I ran it, it says profile doesn't exist---this is right. Then I changed it back to the right profile, it didn't raise any error!
So, this means EXEC msdb.dbo.sp_send_dbmail did go through, but then why it didn't show up? I checked in the msdb.dbo.sysmail_mailitems table, confirmed it is not sent out.
Can anyone suggest where should I go? or create some work around?
Thank you for your time. This has been driving me nut.
BTW, let me post my sp here so you will see how simple the sp is:
begin try
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL 2005 Email',
@recipients = 'someone@somewhere.com',
@body = @body1,
@subject = 'You have unread firecall requests in Privileged Access Attestation (PAA) system, please take action.' ;
end try
begin catch
insert into ErrorLog (Msg, Error, [User], CreatedOn) Values(ERROR_MESSAGE(), ERROR_STATE(), suser_sname(), getdate())
end catch
February 8, 2010 at 2:30 pm
I finally am able to resolve this issue:
All I need to do is:
The job is type of "T-SQL", go to edit the step in that job, two options in the left pane: General and Advanced
In General, there is a type dropdownlist, choose T-SQL, there is a Run as option but it's empty --- this is what is misleading people
Go to Advanced: at the bottom of the right pane, you will see Run as user option is available, choose correct user there and off you go!!!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply