Sql-server – SQL Server Agent & sp_send_dbmail failing on permissions

Have you checked which SQL user is actually running the query? Depending on the permission level, SQL Agent may be allowing the user to 'impersonate' a higher level account.

Create a job with the T-SQL step:

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME();

Make it log output to a table or text file and review which user is actually running the final query. If it's not the user you think it should be, then you'll need to trace back to work out where the impersonation is coming from.

I have ran into problems on CRM Dynamics databases using filtered views, where specified CRM users can see their filtered data. If the job is using a system account impersonation it'll return no data as the system account doesn't exist as a CRM user.

Dan,

I'm assuming the account running the agent job is a sysadmin. In this case, the DEV instance will need to have its' service account or service sid for the DATABASE ENGINE given access to the folder.

SQL Server agent doesn't do the restore, it connects to SQL Server and impersonates the account if non sysadmin and then continues. In this case you're connecting to the database engine and issuing a RESTORE DATABASE command which will be processed by the engine. SQL Agent isn't touching the backups, the engine is.

Edit: Explaining the posted agent output

Executed as user: NT SERVICE\SQLAgent$MyAppDEV.

This is letting you know that the Agent job was executed as the SQLAgent$MyAppDEV local account. This is most likely due to the job owner being sysadmin.

Cannot open backup device 'E:...MyApp_backup_2016_02_24_092912_0136837.bak'.

Here is the actual error, we can't open the file. We don't know why yet.

Operating system error 5(Access is denied.).

This is explaining why we can't open the file, the OS returned error 5. This can be validated by checking the OS return code by executing net helpmsg 5 on a command prompt.

[SQLSTATE 42000] (Error 3201)
RESTORE DATABASE is terminating abnormally.
[SQLSTATE 42000] (Error 3013).
The step failed.

Some extra notes about what is happening, but this isn't relevant to the actual error or issue.