This blog post is something of a lesson in humility. I like to think I know Dynamics GP fairly well, but it always goes to show that one should never get too big for one’s britches. The good news is that the worst damage was the time spent surfing Google for answers – in the long run, a small price to pay for a valuable life lesson.
Not long ago, I was doing an upgrade for a client where I ran into issues when trying to recreate a GP login. To give some background, I had brought this client through an upgrade from GP 10.0 to GP 2013, and then from GP 2013 to GP 2015 R2. On the day of Go-Live, we had to add a new user, which had previously been added to the test environment. (This was the same test environment that we were switching on as the Live environment.) These databases had been written over when doing the backup/restore/upgrade process prior to bringing the Dev system into production, thereby erasing the user record that was created.
The morning of Go-Live, as per the usual practice, I went to Tools>Setup>System>User and entered in the appropriate information for the user – for our purposes, we’ll call the user “ABCD.”
When I hit the “Save” button in the user window, the following error came up:
“A SQL login with this user name already exists. Choose a different user name.”
Highly intriguing, but it didn’t give too much information, beyond telling me that this user name was already in the database somewhere. I then ran a Dexsql.log, and got a better error:
/* Date: 12/12/2016 Time: 12:29:10
SQLSTATE:(37000) Native Err:(15025) stmt(159597616):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal ‘ABCD’ already exists.*/
/*
Hm. Interesting, but it still didn’t give me a direct table to search. I checked the Logins folder under the Security folder, but the ABCD user wasn’t showing up.
I then found a script thanks to the Dynamics Community which was designed to clear both the Dynamics and company database user tables via SQL Server Management Studio:
DECLARE @USER as varchar(30)
SET @USER = ‘User_name’
DELETE COMPANY_1..SY01401 WHERE USERID = @USER
DELETE COMPANY_2..SY01401 WHERE USERID = @USER
DELETE COMPANY_3..SY01401 WHERE USERID = @USER
DELETE DYNAMICS..ACTIVITY WHERE USERID = @USER
DELETE DYNAMICS..SY10500 WHERE USERID = @USER
DELETE DYNAMICS..SY08000 WHERE USERID = @USER
DELETE DYNAMICS..SY60100 WHERE USERID = @USER
DELETE DYNAMICS..SY01600 WHERE USERID = @USER
DELETE DYNAMICS..SY01400 WHERE USERID = @USER
DELETE DYNAMICS..SY01403 WHERE USERID = @USER
DELETE DYNAMICS..SY60100 WHERE USERID = @USER
DELETE DYNAMICS..SY10550 WHERE USERID = @USER
USE DYNAMICS
DROP USER User_name
GO
USE COMPANY_1
DROP USER User_name
GO
USE COMPANY_2
DROP USER User_name
GO
USE COMPANY_3
DROP USER User_name
GO
/*
Still, when I went back in to recreate the user, I got the same error. Then, through some Google-fu, I found out that there was an orphaned SID sitting out there. If I ran select SUSER_ID(‘ABCD’), I got the SID returned. Based on that SID, I then ran select * from sys.server_principals where SID = ABCD, and I could finally see the orphaned record.
I found I could also see the record if I ran a select * from sys.server_principals where name = ‘ABCD’
However, when I tried to run a statement to delete this record, I got the following message:
“Ad hoc updates to system catalogs are not allowed.”
It seemed that the system protects certain tables from being updated/modified under normal circumstances. I found that typically if such a statement needed to be run, you would have to enable a Dedicated Admin Connection (DAC), which involves starting the server in single-user mode.
At this point, I knew I needed a different approach. As time was of the essence, I reached out to one of my colleagues, and he was able to take a look at the SSMS. He’d been poking around for less than two minutes when he went to one of the company databases, expanded it, expanded the Security folder, and expanded the Users folder. Here it was that we found the culprit: there was an orphaned user record that appeared to have been deleted from everywhere else in SQL except the company databases, despite the scripts above. We were able to right-click on the login in question and delete it via the Graphical User Interface (GUI). After doing so, we were able to create the ABCD login without receiving further errors.
The moral of the story is that sometimes the query editor can’t be trusted, and you should check the GUI anyway. That, and to never get too big for your britches!
[avatar user=”jnorberg” size=”thumbnail” align=”left” /]JOHN NORBERG | Business Software Consultant
After working a variety of jobs through college, from dishwasher at an Italian café to gravedigger and caretaker at a cemetery, John graduated from North Dakota State University and Minnesota State Community Technical College with degrees in Philosophy and Information Technology. In 2014, John began working as a Support Engineer at Microsoft in Fargo, ND, the birthplace of Dynamics GP. He discovered a passion for delivering excellent customer service, and he often lead the team in cases resolved and positive feedback. After two years working Technical Support for GP, John accepted a position at KTL Solutions as a Business Software consultant. Unlike his previous position which had afforded few personal meetings, the deep interaction with clients at KTL Solutions has allowed John to identify and analyze their problems, leading to the implementation of solutions suited to their individual needs.
