ssrs report on msdb
Posted on 2015-02-24
Hi - I'm having a permissions issue I can't figure out. (SQL Server 2012 Standard)
In SSRS, I have a non-user domain login as the credentials in a shared data source against the MSDB database. The goal is to give some general users access to a report that displays emails that went out from the server. I created a view in MSDB to run SELECT * FROM dbo.sysmail_allitems and based the report on the new view (a view on a view - I know...)
The data source tests OK. But, when I try to run a report based on that data source, it is empty. When I replace the credentials in the shared data source with my own, the report works fine.
I've given the non-user membership to DBO, DBMailUserGroup, data reader, everything one at a time, and all together in different variations. (my ignorance here may be showing: but DBO??? that "should have" been it??? not sure why that didn't work.)
Hope there is some wisdom out there - I've left mine all over the floor.