Solved

ssrs report on msdb

Posted on 2015-02-24
2
176 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
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

679 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