title: Sending e-mail notification when job fails doesn't work
body: <ol>
  <li><p>I prepared scripts for sysadmin, for creating and configuring database mail profile, account and operator. He run the script, so I created temporary job, which fails. Then I set e-mail notification when the job fails. I run the Job, but no e-mail has been sent.
  Then I tried to run <code>msdb.dbo.sp_send_dbmail</code>  procedure, using the same operator and I received the e-mail. What can be the reason of not sending e-mail when the job fails? Which procedure is being run in case of using e-mail notification when the job fails - is it different than <code>sp_send_dbmail</code>?</p></li>
  <li><p>The other thing is that in database mail log no there is no information about sending e-mails status (it is empty) - maybe I have no permissions to see the log?</p></li>
  <li><p>I set the profile as public, running <code>dbo.sysmail_add_principalprofile_sp procedure</code>, but when I try to send the e-mail running <code>msdb.dbo.sp_send_dbmail</code> a logged as dbowner I get an error: <em>The EXECUTE permission was denied..</em> Why it only works when I am logged as my user?</p></li>
  </ol>
  
  <p>I'm using SQL Server 2008.</p>
  
  <p>Here is a T-SQL code, that I passed to sysadmin for creating and configuring database mail.</p>
  
  <pre><code>-- Create a Database Mail account
  
  EXECUTE msdb.dbo.sysmail_add_account_sp
      @account_name = 'Mail Account',
      @description = 'Mail account for administrative e-mail.',
      @email_address = 'xx@xx.pl',
      @display_name = 'Job failure notification',
      @mailserver_name = 'xx.xx.xxx' ,
      @username = 'aaa',
      @password = 'xxx'
  
  -- Create a Database Mail profile
  
  
   EXECUTE msdb.dbo.sysmail_add_profile_sp
      @profile_name = 'Database Mail Profile',
      @description = 'Profile used for job failure notifications.' ;
  
  -- Add the account to the profile
  
  
  EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
      @profile_name = 'Database Mail Profile',
      @account_name = 'Mail Account',
      @sequence_number =1 ;
  
  -- Enable Mail profile in SQL Agent
  
  
  EXEC master.dbo.xp_instance_regwrite 
        N'HKEY_LOCAL_MACHINE', 
        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
        N'DatabaseMailProfile', 
        N'REG_SZ', 
        N'Database Mail Profile'
  GO
  
  -- Create new operator
  
  
  EXEC msdb.dbo.sp_add_operator @name=N'SQL Job Failure', 
              @enabled=1, 
              @weekday_pager_start_time=90000, 
              @weekday_pager_end_time=180000, 
              @saturday_pager_start_time=90000, 
              @saturday_pager_end_time=180000, 
              @sunday_pager_start_time=90000, 
              @sunday_pager_end_time=180000, 
              @pager_days=0, 
              @email_address=N'xx@xx.pl', 
              @category_name=N'[Uncategorized]'
  GO
  
  -- Setting profile as public
  
  EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
      @profile_name = 'Database Mail Profile',
      @principal_name = 'public',
      @is_default = 0 ;
  </code></pre>