November 6, 2009 at 3:55 pm
Hi Everyone,
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.
Bill,Charlotte NC
November 9, 2009 at 8:43 am
No reply needed, figured this out. I finally realized that the view sysmail_allitems has a where clause that checks the username of the sent emails and my application user is not the same as my agent job that sent the emails.
I was able to fix this by changing my application db view to join to sysmail_mailitems.
July 14, 2015 at 7:18 am
Agreed. I had a sproc that referenced the view msdb.dbo.sysmail_allitems and no matter how I changed the permissions I could not get it to return records. When I changed that sproc to reference the table msdb.dbo.sysmail_mailitems I had access to essentially the same data.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply