Solved

ssrs report on msdb

Posted on 2015-02-24
2
180 Views
Last Modified: 2015-03-03
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
Comment
Question by:bill_ee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 40630659
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
 

Author Closing Comment

by:bill_ee
ID: 40643368
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 9 28
SQL Job Failed 6 31
Populate fields in group based on first record in group 4 15
SQL Server Reports (SSRS 2014) - Reports Timing Out 11 116
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question