CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqldatabase-mail
OMG Ponies
OMG Ponies

Reputation: 332731

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?

Upvotes: 3

Views: 2716

Answers (2)

Martin Smith
Martin Smith

Reputation: 453837

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.

Upvotes: 7

codingguy3000
codingguy3000

Reputation: 2845

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.

Upvotes: 0

Related Questions