Skip to content
Login | Support
KTL Solutions
  • Home
  • Platforms
    • Microsoft Office 365
    • Microsoft Dynamics 365 – ERP
    • Microsoft Dynamics 365 – CRM
    • Microsoft Azure
      • CMMC Preparedness
    • Microsoft Power Platform
    • Microsoft Government Cloud
      • GCC
      • GCC-High
      • Azure Government Cloud
  • KTL Products & Services
    • KTL 360 Managed Services
      • KTL 360 Commercial Services
      • KTL 360 Government Services
    • KTL Custom Development
    • KTL Products
  • Company
    • About Us
    • Careers
    • Events
  • Blog
  • Contact Us
  • Home
  • Platforms
    • Microsoft Office 365
    • Microsoft Dynamics 365 – ERP
    • Microsoft Dynamics 365 – CRM
    • Microsoft Azure
      • CMMC Preparedness
    • Microsoft Power Platform
    • Microsoft Government Cloud
      • GCC
      • GCC-High
      • Azure Government Cloud
  • KTL Products & Services
    • KTL 360 Managed Services
      • KTL 360 Commercial Services
      • KTL 360 Government Services
    • KTL Custom Development
    • KTL Products
  • Company
    • About Us
    • Careers
    • Events
  • Blog
  • Contact Us

John Norberg

An Orphaned User Record Teaches Me a Good Lesson

  • February 13, 2017
  • , 11:20 am
  • , ERP, How Tos

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.

Share this post

Tags
365 2013 2015 2016 accounting BI blog Business Business Intelligence Cloud CMMC CRM Data Development Dynamics Dynamics 365 Dynamics CRM Dynamics GP ERP Excel GP How To ISV KTL KTL Blog KTL Solutions Management Microsoft Microsoft Dynamics Microsoft Dynamics CRM Microsoft Dynamics GP Office 365 power Power BI Project Project Management Reports Sales Security Software Solutions Technology tools User User Conference

Recent Posts

  • KTL Solutions recognized as a finalist for 2022 Microsoft Defense & Intelligence Partner of the Year
  • Webinar Recap: Grow Your Business with KTL and TechnoMile
  • Easily Create Your Own Custom Estimate for Business Central With Our Pricing Calculator
  • The basics of CMMC 2.0 and preparation recommendations
  • Webinar Recap: CMMC News for the DIB

Categories

  • Azure Government
  • BI
  • Blog
  • Business Central
  • CMMC
  • CRM
  • Customization
  • Dynamics 365
  • ERP
  • Events
  • Exclusive Content
  • GCC-High
  • How Tos
  • KTL Products
  • Managed Services
  • Microsoft 365
  • Microsoft Dynamics GP
  • Modern Workplace
  • Project Management
  • Technology
  • Uncategorized

Related Posts

KTL Solutions recognized as a finalist for 2022 Microsoft Defense & Intelligence Partner of the Year

KTL Solutions today announced it has been named a finalist for the 2022 Microsoft Defense & Intelligence Partner of the Year Award. The company was honored among a global field of top Microsoft partners for demonstrating excellence in innovation and implementation of customer solutions based on Microsoft technology.

Read More »
June 28, 2022

Webinar Recap: Grow Your Business with KTL and TechnoMile

KTL and TechnoMile recently co-hosted an informative webinar for Aerospace & Defense and Government Contractors with valuable information on growing your business. The webinar detailed

Read More »
April 20, 2022

Easily Create Your Own Custom Estimate for Business Central With Our Pricing Calculator

Easily Create Your Own Custom Estimate for Business Central With Our Calculator

Read More »
February 4, 2022

The basics of CMMC 2.0 and preparation recommendations

Originally posted in the Microsoft Public Sector Blog by Justin Orcutt, Jan 11, 2022 In This Article:  On November 4th, 2021, the Department of Defense

Read More »
January 13, 2022
Delivering tailored solutions so you get the most out of your technology
Facebook-f Twitter Linkedin-in Youtube

Quick Links

  • Platforms
  • KTL 360 Managed Services
  • KTL Custom Development
  • KTL Products

About

  • Company
  • Careers
  • Blog
  • Support Log In

Newsletter

Sign up for our mailing list to get the latest updates and offers.

Subscribe to Our Newsletter
  • 866.960.0001

© All rights reserved

Terms of Use | Privacy Policy

Designed by Papersword B2B

KTL Solutions is proud to announce it has been named a finalist for the 2022 Microsoft Defense & Intelligence Partner of the Year Award. 

We were honored among a global field of top Microsoft partners for demonstrating excellence in innovation and implementation of customer solutions based on Microsoft technology.