Dynamics GP security conundrum

Today I was helping a customer install Post Master Enterprise for Dynamics GP in a development environment for testing.  It should have been a simple process, taking maybe 15 minutes--but after an hour, we had to stop and reassess.

The Post Master Enterprise installation requires the 'sa' SQL Server login, or an equivalent login, which allows it to create SQL tables and stored procedures.   The IT admin at the customer site wasn't sure if he had the correct sa password, so we attempted to login to Dynamics GP first.  He entered the sa username, then tried to paste in the long password.  But the password would not paste.

If you've ever had a problem pasting a value into a GP field, you probably know that a very common reason is that the value you are pasting is longer than the GP field length.  So when the password didn't paste, I was puzzled.  I had never checked the max password length for GP--turns out it is 15 characters. 

When the IT admin counted the characters in the sa password he had documented, it was longer than 15 characters.  Hmmmm.  That is pretty strange.

Since we wanted to login to GP as 'sa' to setup a new GP user, the IT admin reset the sa password on the development SQL Server, making sure to keep it 15 characters.  With that done, he launched GP and logged in as 'sa'.

GP eventually loaded, but the annoying "you do not have permission to open this window" message appeared several times.  We then sat and waited for the menus to display.  The Reports menu appeared, but nothing else.  When he clicked on the Dynamics menu, there were very few options.  We were unable to access any of the Setup menus when logged in as 'sa'.

Not good.

So then I asked if he knew the DYNSA password.  Unfortunately, the dev server had been setup by a former employee, so he didn't know the DYNSA password.  And unfortunately, the DYNSA password cannot be changed from SQL Server Management Studio--it must be changed within GP.

So...the sa login doesn't have any permissions in GP.  And we can't login as DYNSA.  Quite a conundrum.  Without sa permissions in GP, without the DYNSA password, and without knowing any other GP passwords for the dev environment, there wasn't much we go do to get into GP, let alone modify user access for testing with Post Master.

That's when I stopped the process and scheduled a second call--things just weren't going well.

After weighing some options, I figure that the easiest initial approach will be to try and modify the GP security records in the DYNAMICS database to try and restore Power User access to the 'sa' login.

I'll start by checking the security roles assigned to the sa login:

SELECT * FROM DYNAMICS..SY10500 WHERE USERID = 'sa'

And out of curiosity, I'll want to see if any users are assigned to Power User:

SELECT * FROM DYNAMICS..SY10500 WHERE SECURITYROLEID = 'POWERUSER'

And then I'll try and insert a record into SY10500 to try and assign the Power User role to the 'sa' login.

INSERT INTO DYNAMICS..SY10500 (USERID, CMPANYID, SECURITYROLEID) VALUES ('sa', -1, 'POWERUSER')

This, in theory, should allow us to login to the Fabrikam company with 'sa' as a Power User.

From there, I'll have to see what other surprises await us.

The client could also restore the DYNAMICS database to the dev environment--and we may just do that to refresh the environment while solving the security issue.  But I haven't yet seen their production environment, so I don't know if there are any oddities there as well.


Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com