/build/static/layout/Breadcrumb_cap_w.png

SQL to switch User IDs

The situation: After the upgrade to v6 one of our techs ran a manual user import. It was misconfigured so that the LDAP UID was linked to samaccountname and username was linked to CN changing all of the usernames from, say, jsmith to John Smith, and breaking the link to the LDAP username, breaking the authentication for the effected users. The corrected import, instead of re-updating the username, created new users with the proper login names. However, this means that things like ticket ownership are broken, since those are linked by the ID field of the User table.

Now, I know just enough SQL to break something, but the script I came up with is:

/* Step 1: Change the old ID (1234) to correct username. */
UPDATE `ORG1`.`USER`
SET `ID` = '1234'
WHERE `USER_NAME` = 'jsmith'; /* Step 2: Change the new ID (5678) to broken username. */
UPDATE `ORG1`.`USER`
SET `ID` = '5678'
WHERE `USER_NAME` = 'John Smith';

The only way I know how to run SQL update queries is through Ticket Rules, so my questions are: Will this even work? Is there a better way? Is there a way to formulate a query to do this for all of the affected users (something along the logic lines of "IF John Smith and jsmith exist in USER_NAME, switch ID")? 


1 Comment   [ + ] Show comment
  • If you made backups prior to this you can roll back by doing a restore if did not do a backup I would open a ticket kace support can go in on the backend and fix this easier then you can. - SMal.tmcc 10 years ago
    • I tried that and they referred me to their Professional Remote Services. I would like to avoid a $500+ bill for a simple script. - tdickinson 10 years ago
      • yea I don't blame you, not a SQL expert either, hopefully another Ninja who is will have an idea - SMal.tmcc 10 years ago

Answers (2)

Answer Summary:
Posted by: MacDude 10 years ago
Fifth Degree Brown Belt
0

Is LDAP ID set to ObjectGUID for the import? 

FWIW, here is how I generally recommend mapping the fields (first four required):

LDAP ID = objectGUID
login = samaccountname
name = name
email = mail
location = physicalOfficeDeliveryName
work phone = telephoneNumber
 

 

The SQL query & update probably is possible, but I'm not going to be much help there. 

 


Comments:
  • I forgot to mention (and have subsequently updated my question) that the misconfigured import also linked LDAP ID to samaccountname, thus the duplication of the users once the corrected import was done. - tdickinson 10 years ago
Posted by: tdickinson 10 years ago
Orange Senior Belt
0

Through dangerous experimentation, I have discovered the answer: Yes, it does work but you can only run one update query at a time. So I created separate rules for each step. I still have to do it one user at a time, but it's a solution.

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ