DYNGRP and Service Pack Updates

(0) Share

Report

Posted on by drummerboy_10

I recently updated our GP2010 installation to SP4 (in preparation for an upgrade to GP2015). After the update, the sqlReporting user threw an EXECUTE permissions error when trying to run a SSRS report from our reporting server. The natural response was to claim GP caused this problem, but I wasn't so sure. As is almost always the case, the report worked before the update occurred.

The sqlReporting user is a user in SQL Server but was not a user created through the Dynamics GP application therefore was not assigned to the DYNGRP role automatically. It was created externally to Dynamics GP and was assigned the db_datareader, db_datawriter and (I assume) the DYNGRP database membership roles during creation.

Here's my question:

Is there a process which does a comparison between the users created in Dynamics GP and assigned to the DYNGRP vs. users who are assigned to the DYNGRP role and don't have a matching user record in Dynamics GP during a service pack update? And is it part of the process to remove those SQL users from the DYNGRP if a matching Dynamics GP record is not found?

This is the only scenario which makes sense to what caused this particular user to be removed from the DYNGRP.

I certainly appreciate any feedback.

Thanks,

Sean

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Sean,

Not that I am aware of. Think of an instance where a user was created in GP and who is no longer with the company. The DBA says oh this user isn't with the company so he deletes the SQL Login. What doesn't happen is that the login still remains at the database level. There are some SQL scripts I have that might help you identify these users.. But as far as the upgrade goes there isn't any logic that I have ever seen executed that does this in all of our upgrades from GP 9 - 2013 R2 and several SQL server moves.

Was this reply helpful? Yes No
drummerboy_10 on at

Like (0)

Report
Copy link

Link copied!

Hi Jonathan -

Thanks for the reply! I didn't think there was, yet it was the only thing which made any logical sense when I tried to piece together why this particular user would have been removed from the DYNGRP role. As soon as I added them back, the reports rendered data as expected.

I'm sure it's not worth the effort of a SQL Trace during the next service pack or version upgrade, just wanted to see if anyone else had encountered this before.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Actually if this was a fairly recent occurrence and you haven't restarted your SQL server you might be able to pull that information from the basic default trace that SQL runs.

blogs.technet.com/.../sql-server-default-trace.aspx

This link outlines it for SQL 2005 but the functionality is the same for 2008, 2008 R2, 2012, and I believe 2014 but it's going to be deprecated in future versions of SQL as noted in this article technet.microsoft.com/.../ms175513.aspx

Was this reply helpful? Yes No
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hi Sean

your issue might have been induced by the fact that you applied a whole SP to GP... which in several cases resets a lot of settings in the system. One of them being to re-deploy sometimes the whole collection of SSRS and Excel reports when you do perform the GP Utilities.. in such case the security may have been triggered back to its default after the process completed.. Pay attention to that when you're going to upgrade to 2015 as well...

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report
Copy link

Link copied!

Had not considered that the SSRS pack gets replaced or updated when you apply an SP to the system.

Interested to know if this does fix the problem.

Was this reply helpful? Yes No
drummerboy_10 on at

Like (0)

Report
Copy link

Link copied!

I had not considered the SSRS report pack getting replaced, and it's a great thought/suggestion, but I didn't re-deploy the reports post Service Pack update. The SSRS report they were attempting to run is not an "out-of-the-box" standard GP SSRS report - I do believe it was a in-house built report, which is just another variable in this rather precarious equation.

Was this reply helpful? Yes No