No rows in MSDB.dbo.sysmail_faileditems

Question

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?

Answer 1

You could be looking at the wrong version of the table. Tables can be created under different user names. so database.dbo.mytable is different from database.klowrey.mytable.

Look for your table in Object Explorer. See if there are two tables with the same name under different users.

Answer 2

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.