Hi,

Firstly, apologies if this post has been made in the wrong group.  Running SQL Server 2012.  I'm attempting to add a SQL Server Agent Job which calls a stored procedure that sends a Database Mail message.  The SQL Server and SQL Server Agent both run under the NT AUTHORITY\NETWORK SERVICE account.  The Database Mail service has been enabled, and a public profile created.  When running the stored procedure manually, I receive no errors - everything runs as expected and I do receive an email containing the expected information.

I've created the job, job step, job schedule, attached the schedule to the job, and assigned the job to server, all using T-SQL without error.  I've also enabled the mail profile on the SQL Server Agent, and I know that part is working because when the job fails, I get an email notification of the failure.

I've checked the command text in the job step and parsed it within the SQL Job Step Edit window to confirm, it shows as parsing correctly.  However, when I manually run the job itself, I get the following:

Execution of job failed.  See the history log for details.

I check the history log and it shows:

[474] Unable to refresh Database Mail profile Database Mail Profile. (reason: ) (Not a typo, the history log shows no reason)

[260] Unable to start mail session.

[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect

The command text on the failing job step is as follows:

DECLARE @date [varchar](10)

SET @date = CAST(GETDATE() AS [varchar](10))

EXEC [dbo].[GetExceptions]
    @company = 'MyCompany',
    @checkDate = @date

With regard to the date value being passed as varchar: This stored procedure is used to check for exceptions against multiple databases on this server (hence the company parameter) via dynamic SQL.  I'd much prefer to use proper data typing but this is the only way I could get it to work.

Does anyone have any suggestions on anything else I could check, or insights into why this is failing?  Any help is greatly appreciated!

Best Regards

Brad

I am not sure if this really helps but I would do follow the below steps:

1. make sure sql server agent has database mail enabled(rigt click on server agent--properties--alert system--enable database mail and choose right profile.) and RESTART THE SQL SERVER AGENT.(i know you said it i working, but sometimes,  restart the sql server agent might fix)

2. check agent error log and check if any error messages.

3. run the command text you mentioned in sql server and see if it is working.(i know you said it is working but just to make sure).

4. make sure sql server agent service user has permissions to run database mail in msdb. check this https://msdn.microsoft.com/en-us/library/ms186358.aspx

5. Check the output from select * from msdb.dbo.sysmail_log and if it says anything

6.it does not look like the job log is getting truncated but to make sure, get the job step output to a text file.to do this, edit the job step and on the job step properties click on advanced,  enter path to the output file. this will give the complete output for the step.

Hi Stan,

Thanks for the suggestions.  Most of those were already taken care of, but I did go ahead and enable output logging.  Below is the result:

Msg 916, Sev 14, State 1, Line 2: The server principal "sa" is not able to access the database under the current security context. [SQLSTATE 08004]

Which is even more confusing.  The job, steps and schedule were all created while logged in to the server under the sa account.  As was the stored procedure being called.  No WITH EXECUTE AS clause is used in the procedure.  While digging in to this problem, I saw that the debo user in this database was mapped without login.  So after doing some research I used ALTER AUTHORIZATION ON DATABASE:: myDB TO sa, and that appears to have fixed that problem - the sa account is now mapped to the dbo user.  The sa account also has the db_owner role assigned to it.  However, even after all of this, I still get the above error message when attempting to run the job.  Any other ideas?

Thanks!

Is your instance a mixed authentication instance with the sa-account set ? Which account owns the agentjob? Does that account (if not sa) own executerights in the database where you keep the mailsendingscript ?

Hi Stan/Flemming,

It appears as though the issue was related to impersonation and the fact that the SP in the job step accesses databases other than the one holding the SP.  Credit goes to Erland Sommerskog - when I removed the Run as User value from the Job Step it proceeded to run without issue.  I may have additional issues when moving to production tomorrow, but I think for the scope of this question we have it fixed.

Thanks to you both, and Erland, for your help!  Greatly appreciated!

• Marked as answer by 2012S4 9 hours 22 minutes ago