I am assuming that you have setup database mail? If so, then please continue.
So lets imagine that this is your TSQL job step. The query is trivial (but is actual quite useful when learning about who is running your SQL Agent jobs. Ill leave that to another blog post though.)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = 'your@email.com',
@subject = 'Sent from SQL Agent Job',
@execute_query_database = 'DB_TO_RUN_QUERY_ON',
@query = N'
SELECT
ORIGINAL_LOGIN() AS [ORIGINAL LOGIN]
,SUSER_SNAME() AS [EFFECTIVE LOGIN]
,USER_NAME() AS [DB USER]',
@attach_query_result_as_file = 0;
Under the advanced page of your job step, you need to set Run As User to be a user that is a member of the DatabaseMailUser database role in the msdb database. For ease of use and to get things working I chose 'dbo' If you dont do this, then the TSQL will run under the SQL Agent service - and by default, this wont be a member of DatabaseMailUser database role in the msdb database.
The next thing was to set the database drop down list field to be msdb.
I thought this was totally unexpected because I had already fully qualified the stored procedure with DB and schema. i.e. msdb.dbo.
And no, it makes no difference if you put a 'USE msdb;' at the top of your TSQL job step!
MSDB MUST be set on the database drop down list.
