Drupal - How to Change Your User ID

How to change your user ID (without jacking everything up)

We're going to show you how to update your Drupal user ID without wreaking havoc across your site.  If you are comfortable administering your database tables updating a user ID for a particular user is not all that difficult.  The key is updating all the tables where the variable for user ID, "uid", takes place.  At the time of this writing we were not aware of a Drupal module that automates this process.  If you know of one, or are interested in working on one, please post below.

Why would you want to change a user ID?

The reason we initially came across the need to change a user ID is due to the fact that we were in the process of embedding a forum installation (phpBB) within Drupal (read our article on integrating phpBB with Drupal for the details).  phpBB, for some odd reason, required the use of Drupal user ID #2 as the primary administrator.  We therefore had to regain control (and therefore change), user ID #2. 

Which tables do I need to update?

You'll need to update the following tables (using, for example, phpMyAdmin), in order to cover the most common instances of "uid" (if you are using additional contrib modules you'll want to search for additional tables making use of "uid).  Important: Before proceeding, as with any database updates, make sure you backup your database in case something goes awry!  Once you're ready, execute the following SQL command on the tables below (in this example we're changing user ID 5 to user ID 2).  Note:  You need to make sure you're updating to a user ID that does not already exist!  If the user ID you wish to update to already exists, simply take control of that user and create a new user for the former user.  If it does not exist, proceed with the following:
UPDATE node SET uid = 2 WHERE uid = 5;
You'll want to update the tables:

• users
• user_roles
• update node
• node_revisions
• comments
• subscriptions

Voila :)  If you updated all instances of uid 2 you should see all nodes intact on your site.  If you forgot some, you may see instances of "n/a" in place of posts, or modules behaving oddly.  If this is the case, restore your backup and find the additional tables utilizing "uid" that need updating via the above SQL command.