Search code examples
sqlsql-serversql-server-2005t-sqldatabase-mail

No rows in MSDB.dbo.sysmail_faileditems

Initially I was getting a "no SELECT privilege" error when attempting to query contents for a report I need to create. So we granted DatabaseMailUserRole in MSDB to my account - I see columns, but none of the data we know to exist.

What am I missing that needs to be done for my account to see the data?

Solution 
  • select OBJECT_DEFINITION(OBJECT_id('sysmail_faileditems')) 
AS [processing-instruction(x)] FOR XML PATH('')

    Shows sysmail_faileditems definition is

    SELECT * FROM msdb.dbo.sysmail_allitems WHERE sent_status = 'failed'

    Looking at sysmail_allitems. The definition of that is

    SELECT ...
FROM msdb.dbo.sysmail_mailitems
WHERE (send_request_user = SUSER_SNAME()) 
      OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)

    So it looks like you need to be in the sysadmin role to see all results or the sending user to see filtered results.