Suddenly today no one's Metric work. They all see this message in the Metrics frame. SSRS is still working and is accessible to all users using their domain credentials. Where do I even begin to look for the cause of this?
*This post is locked for comments
Thanks for the update and letting us know what you found Richard. Definitely not a common thing we see, so this will help having this information out there.
OK, just for the heck of it I tried the SSRS reports for Fabrikam and they fail in the same fashion. I then tried to run reports that use the same tables in the same databases using the same data sources but we written by me and they work fine. So it has something to do with security for all the GP generated reports. Maybe I will try re-deploying the reports because this makes no sense. Re-deploying the reports did the trick. What made this even more strange was when I look at the Reporting Tools Setup screen it told me the SSRS reports for this company were not deployed even though they were. I have no idea how this happened but hopefully this will help others if they run into a similar issue.
No, the only schema that should be marked there is rpt_alluser.
Under that the listed members of that role, by default, would be:
-rpt_Executive
-rpt_Human resources administrator
-rpt_payroll
-rpt_power user
Any users assigned this role would be listed as well.
Here is a screenshot of the rpt_all_user role. Shouldn't DYNGRP be checked?
That is curious......
Normally, as far as SQL SRS report permissions, we have the following setup per user:
If they were deployed in Native Mode, there are three places where permissions need to be set when GP SSRS reports are deployed in Native mode:
1. On the Report Manager URL, Home > Site Settings > Security, users are setup either as 'System Administrator' or 'System User' permissions.
2. Still on Report Manager URL, Home > Properties tab > Security, users are setup with either Browser, Content Manager, My Reports, Publisher, and/or Report Builder permissions.
3. Lastly, user's Windows account will need to be assigned to the RPT_ roles under the Dynamics GP databases in SQL Server Mgmt Studio.
The RPT_All group or RPT_All user database role, under the DYNAMICS database, is required for all users to run SRS reports.
Under the company databases, assign users to the RPT_ roles they need to access the data for the SRS reports they have access to.
The SQL SRS logs may be another place to look for more information on this type of error. Those are located at the C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\LogFiles\
Thanks
Over the weekend when no one is using SQL I am going to turn on the SQL Profiler and try to access this SSRS report. I have checked every privilege you have mentioned and they all look correct. The only domain user that can run the GP SSSRS reports is the domain admin. I have set myself as a SQL sysdadmin and that did not help nor did specifying myself in any of the roles. I have never had to adjust any of these security settings in the past for any of our clients. Why all of a sudden for this client I have no idea. I can do a SELECT * FROM VENDORS just fine in SSMS just not through SSRS or GP. This one has got me scratching my head and will keep me awake at night until I find what it is!
If you go into the properties of the Vendors view, for the company database that is seeing this issue, and look at the permissions, is DYNGRP showing the SELECT permission marked?
Other than that, that would be the things to check in that the DYNGRP role has SELECT permissions for any GP views, the SELECT, INSERT, DELETE and UPDATE permissions to any GP tables and EXECUTE permissions to any GP stored procedures. Then, the user needs to be assigned to the DYNGRP role.
That being said, if the error is a result of trying to access SQL SRS reports, it may not be looking at the DYNGRP role, since the SRS reports use RPT_ role permissions, and it may be a matter of the user needs to be assigned to.
As a test, if you assign the user to the RPT_Accounting Manager role in the company database they are logging into attempting to view SRS reports via the Metrics, does that change this error?
Well they are all members of the DYNGRP and always have been. I added the report role to my domain account and logged out and back in and this issue remains.
This works about the same way. The 'Vendors' object is a SQL View. By default, the DYNGRP role and various RPT_ roles will have the SELECT permission for it. The user(s) would need to be assigned to either DYNGRP or one of these RPT_ roles.
OK, that fixed the domain admin. The users are now getting this:
I have run the GRANT.SQL script on the company database. What could have caused this sudden change? I have never had to do any security changes like this before. These reports also do not run using SSRS.
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,432
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
