Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

ssrs report on msdb

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.

Thanks,

Bill
0
bill_ee
Asked:
bill_ee
1 Solution
 
Ryan McCauleyCommented:
The MSDN page on this feature (https://msdn.microsoft.com/en-us/library/ms175056.aspx) details the rights required (at the end, under "Permissions"), but here's what it boils down to:

 - If a user is a member of the sysadmin fixed server role, they can use this table to view all mail, regardless of who sent it
 - If a user is a member of the msdb database profile called "DatabaseMailUserRole" (or has explicit select rights on the table granted to them), they can view all the mail that their user has sent (but cannot see anybody else's mail)
- Everybody else is denied access to this system table.

I tested this on a SQL 2012 test server that I have and it matches up. What you're describing (a report that shows all mail sent from a server) and is executed directly from this table would require the user executing it to be a sysadmin.

Alternatively, you can set up some replication to move this data to a second table, where the appropriate reporting user is able to view it, and then run the replication job as a sysadmin. If you wanted to do it by hand, you could do something like this every few minutes:

INSERT INTO SomeOtherDB.dbo.MailSentFromServer
SELECT * FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id > (SELECT MAX(mailitem_id) from SomeOtherDB.dbo.MailSentFromServer)

Open in new window

0
 
bill_eeAuthor Commented:
I used the replication idea, but set up an insert trigger on the sysmail_allitems table to refresh the new table, which is reported on.

Thank you!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now