In short I am trying to create a view in my application db that joins to msdb.sysmail_allitems so that I can include the status of sent emails(sent/failed). This will be displayed to my user. I have a sql agent job that generates the emails and I capture the sql email id and some additional data to my application table so I can make the necessary join.
My view works correctly when I run it as myself with a windows login and that makes sense because I'm an administrator.
When the standard security login for my application tries to run the view the column from msdb.sysmail_allitems is blank, because I left join to it. When I run select * from sysmail_allitems under that login no rows are returned even though there is data in the view.
I suspect that I have not correctly granted access to the msdb sysmail_allitems view, or some related and/or underlying objects.
Has anybody done this or know how to do it correctly ? I do not get any permission errors when I query the view, just no data comes back.
What I have done so far:
- Under server security I added the login to the msdb database.
- In UserMappings I checked DatabaseMailUserRole, db_datareader, and public
- I also ran grant select on sysmail_allitems to public but it did not change anything
Is this even possible ?
Thanks for any help.