SQL Reporting Services Permissions/Security
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'. (rsSemanticQueryEngineErro
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
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'. (rsSemanticQueryEngineErro
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
Chris Luttrell
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.
ASKER
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.
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.
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.
ASKER
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?
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?
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.
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.
ASKER
that's the guide I used to set this up (i actually uploaded a copy to my initial post so everyone could follow along).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.