Solved

ssrs report on msdb

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

632 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