I am trying to write a report against the failed mail system views. My UserID on the report server is on the msdb db, and i have added that user to the db_data_reader role in the db. Granted select and view definition to the UserID appropriately in the msdb db. The Report Rendering is not failing but the result set does not return. In the management studio, I am logged in as a different user and get results without any problems. I have also greanted select permissions to the UserID on all of the underlying sysmail....tables that could be utilized by the sysviews.. SYSMAIL_FAILEDITEMS, SYSMAIL_EVENT_LOG
As per Books Online
http://msdn.microsoft.com/en-us/library/ms187747(SQL.105).aspx: > Permissions > Granted to sysadmin fixed server role and databasemailuserrole database role. When executed by a member of the sysadmin fixed server role, this view shows all failed messages. All other users only see the failed messages that they submitted.