SQL Reporting Services Permissions/Security

Posted on 2009-04-08
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 - 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. can I fix this?
Question by:Haze0830
  • 3
  • 2
  • 2
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.

Author Comment

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.
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.  
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails


Author Comment

ID: 24110672
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?
LVL 18

Expert Comment

by:Victoria Yudin
ID: 24110899
I think this might help:

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.  

Author Comment

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).
LVL 26

Accepted Solution

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.

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 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

22 Experts available now in Live!

Get 1:1 Help Now