I have a package that reads from the msdb view sysmail_allitems. I have sysadmin rights and if I run the package, it succeeds and extracts the rows I require. The same package run from a job step using the SSIS proxy account also doesn't fail but extracts 0 rows. The proxy has db_datareader permission on the view.
The DBA assisting me changed the proxy account to sysadmin at which point the job step worked. He then changed it back to dbowner and it stopped working.
How can I permit the job step to read from that table without granting our ssis proxy sysadmin (which seems like overkill)?