Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ssrs report on msdb

Posted on 2015-02-24
2
Medium Priority
?
202 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 1500 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

722 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