Having trouble granting user login select permission on msdb.sysmail_allitems

  • William Plourde

    SSCrazy

    Points: 2035

    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

  • William Plourde

    SSCrazy

    Points: 2035

    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.

  • jacktipsword

    SSC Rookie

    Points: 43

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply