Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Reporting Services Permissions/Security

Posted on 2009-04-08
7
Medium Priority
?
1,551 Views
Last Modified: 2012-08-13
OOOOOOkkkkkkkk. I have a SQL Server 2005 instance with Microsoft Dynamics GP 10 installed on the same box. I have successfully implemented and deployed SQL Reporting Services for Dynamics GP (followed SQL Reporting Services Admin Guide for Dynamics GP - available in mbs.microsoft.com - Dynamics GP Knowledge Base) <---- see the upload, I uploaded the guide.

What I am trying to do now is configure permissions for a few guinea pig users and I am having some issues. I followed the recommended config in the guide (see Chapter 7). My users can successfully browse to the appropriate Company directory and see the reports - but when the click on a report they get this:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'dsPrmItemNumber'. (rsErrorExecutingCommand)
Semantic query execution failed. The SELECT permission was denied on the object 'Items', database 'TEST', schema 'dbo'. (rsSemanticQueryEngineError)

This is obviously a permissions issue but I can't seem to figure it out. I followed the guide to the T - but ran into one roadblock - Step 11 on pg. 35 says that the rpt_All group database role is required for all users to run Reporting Services reports - well I don't have that user role defined in SSMS and I am assuming it's a default user role. The only role I have is rpt_All user which my guinea pig users are assigned.

So...how can I fix this?
ReportingServicesAdminGuide.pdf
0
Comment
Question by:Haze0830
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24101198
I do not have Dynamics to have the exact set up but I would say you are right that it is a permissions issue, we run into similar issues when setting up a new box and forget a permission.  Looking at the steps you are following, if you look back to step 8-9 where you right-click the new login you created and click User Mapping.  You then should make sure the Dynamics database is checked in the top box and sitll highlighted to set the role memberships in the bottom box.  Do you not see the role they mention or something similar in case you changed a name in setting things up for your guinea pigs?  This access is for what ever you are getting from the Dynamics database but your error seems like it came from your database "TEST", so did you follow steps 12 and 13 to select the TEST database and add roles to that database?  If you have a role on that database called rpt_All select it and make sure that the role is granted permissions on the objects it needs.  We create a database role called something like AppUser and grant execute on all stored procedures to it and not select on tables directly.  Sorry it is hard to give exact info without being able to look at your exact setup.  Hopefully some of this might be of help.
0
 
LVL 2

Author Comment

by:Haze0830
ID: 24103126
I took a second look earlier shortly after I posted this question and found that I did not assign the appropriate database role for my report users in the TEST database. All my users are assigned to the DYNGRP role - I assigned the same role and now my reports run. I was hoping to avoid having to do that and instead assign permissions separate from those that are created during the Dynamics setup.

I'm going to have to do a little bit more research on that though. This is really my first production-environment encounter with Reporting Services so I'm still trying to nail down necessary permissions.
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 24110409
DYNGRP in GP typically has all access to all tables, stored procedures, views, etc. in the GP databases.  Read, write, execute, everything.  That might be a lot more than you want to give these users that only need to run reports.  Are your SRS reports based on GP tables, or your owns views/stored procedures?  The only permissions the users should need are to those tables or views or stored procs.  
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 2

Author Comment

by:Haze0830
ID: 24110672
victoria,
I like where you are going with this b/c I know that my current setup grants more access than is required. I want to dial down the permissions a little bit but, like I said, first real experience with SRS using a front end app like Dynamics. I didn't use BITS\VB to create the model/views - I used the SRS Wizard tool that comes with GP - so I am assuming it builds it's own or uses existing views that are created at install? I don't know the intricacies of the tool so I really can't answer beyond that.

what database roles/permissions are required to just run reports?
0
 
LVL 18

Expert Comment

by:Victoria Yudin
ID: 24110899
I think this might help: https://mbs.microsoft.com/downloads/public/GP10Docs/ReportingServicesAdminGuide.pdf

Chapter 7 has a step by step list of what's needed as well links to additional documentation and some general guidelines.  Chapter 3 might also have some useful information.  
0
 
LVL 2

Author Comment

by:Haze0830
ID: 24112348
that's the guide I used to set this up (i actually uploaded a copy to my initial post so everyone could follow along).
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 24112567
I mentioned in my post that you might want to create a Database Role, for example call it ReportsUsers, in your TEST db and assign users to it.  This lets you control what ReportsUsers can access and not lump it in with your DYNGRP role.  
As for "what database roles/permissions are required to just run reports?"  Depending on how you access data for reports, grant exec on stored procedures or select on tables and views to that role.
If you give your users a role like I described then you just need to do a grant on each sp that a report would call directly like this:
GRANT EXEC ON [Schema.]StoredProcedureName to ReportsUsers;
If you access tables and views directly from SSRS then you will have to grant select on each table or view you use.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

604 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