Solved

SQL Reporting Services Permissions/Security

Posted on 2009-04-08
7
1,487 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 26

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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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 26

Accepted Solution

by:
Chris Luttrell earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now