0

After doing some research here and elsewhere, and trying a few troubleshooting attempts, I come to you good folks for help.

In SQL Server 2012, I have a job, with (currently) only one step. That step goes something like this: 

    EXEC msdb.dbo.sp_send_dbmail
       @profile_name = 'SomeProfile',
       @recipients = 'MyEmailAddress@someplace.com',
       @subject = 'Some Subject',
       @query = N'SELECT somedata FROM sometable WHERE something = true,
       @attach_query_result_as_file = 1,
       @query_attachment_filename = 'Weekly_Data.txt'

On the "General" tab of the job, I have the owner as the God-Mode sql user. Should have unlimited privileges.

On the step, the Run As dropdown is empty. I have no idea how to change this.

In the above step code, the @profile_name, I have tried two things. When I did a 'send a test message' attempt using Database Mail in Management Studio, all works well, and the user it used was, we'll say, 'USERX'. I set up Database mail wizard with, we'll say, "USERY". I have tried changing the @profile_name to USERX and USERY with no apparent differences.

Here is the error I'm getting. It's confusing because I haven't (knowingly) asked it to use this user: Executed as user: NT AUTHORITY\SYSTEM. profile name is not valid

I've tried a few things. In Profile Security under Database Mail wizard, the only user visible is USERX (the one the test successfully used to send mail, as outlined above). USERY does not show up. I tried in Database Mail wizard, under View Change etc Account, tinkering around there. The only user that shows up here is USERY! No USERX! I'm very confused. I tried setting USERY SMTP auth to windows, basic, and Anonymous to no avail.

I read that you need to set up in Profile Security the various users like NT AUTHORITY\SYSTEM but that doesn't show up either, and neither does NETWORK.

Any suggestions are most welcome and desperately appreciated.

EDIT: AHA! USERX is a PROFILE, USERY is an ACCOUNT...both are fine. The test email was using the USERX profile under USERY account. So I guess I need to set USERX as the @profile_name but that does not resolve the problem. It still uses NT AUTHORITY\SYSTEM for some reason.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
shubniggurath
  • 956
  • 1
  • 15
  • 31
  • Depending on how this goes, I am very tempted to just scrap it, make a little executable using code that I know will work (it currently works elsewhere) and schedule a task on the server - that seems really unnecessary, but desperate times... – shubniggurath May 12 '15 at 15:13
  • When you run a job, you are running (by default) as the owner of the SQL Server Agent job. When you define the job in SQL Server Management Studio, you can specify another user that you want as the owner of the job. – Gordon Linoff May 12 '15 at 15:13
  • Where are we talking about, @GordonLinoff ? General or within the Step itself? – shubniggurath May 12 '15 at 15:14
  • You can have one account, and then you can add multiple profiles to it. UserX or UserY could be differrent profiles, not different accounts. – Amnesh Goel May 12 '15 at 15:27
  • Yep, got that one just as you sent this @AmneshGoel - why then is it using NT AUTHORITY\SYSTEM ? – shubniggurath May 12 '15 at 15:28
  • I've continued to try various things, using credentials, proxies, ensuring the database mail security profiles are set up correctly, switching out Run As on the step...no changes have allowed the job to successfully execute. – shubniggurath May 12 '15 at 16:52

1 Answers1

0

I'm a fool. IN THE STEP, I told it to use the db that the select query would be using, rather than msdb. It now works.

shubniggurath
  • 956
  • 1
  • 15
  • 31