Today I had to do a change on a clients Admin panel in WordPress only to find that my username had been deleted from the database through the WordPress Admin by one of the other administrators.
Instead of wasting time and asking the client to create me another admin user, I decided to go for the quick approach and simply add another user in the database since we host there web files and database. This technique would work as well if your WordPress website has been hacked or you can’t use the request password reset feature because you no longer have access to the email address that is saved against your username.
Before we start: You should always make a backup of your database before performing any MySQL edits. This tutorial requires a basic understanding of how phpMyAdmin works.
Firstly, you need to login to phpMyAdmin and locate your WordPress database.
Now you will be making changes to the wp_users and wp_usermeta tables. Let’s go ahead and click on wp_users table.
We need to insert our new admin user’s information, so click on the Insert tab as it shows in the image above. In the insert form, add the following:
- ID – pick a number (in our example, we will use the number 99).
- user_login – insert the username you want to use to access the WordPress Dashboard.
- user_pass – add a password for this username. Make sure to select MD5 in the functions menu (Refer to the screenshot below).
- user_nicename – put a nickname or something else that you would like to refer yourself as.
- user_email – add the email you want to associate with this account.
- user_url – this would be the url to your website.
- user_registered – select the date/time for when this user is registered.
- user_status – set this to 0.
- display_name – put the name you like to display for this user on the site (it can be your user_nicename value as well).
- Click on the Go Button
Next, we are going to have to add the values to wp_usermeta table. Click on the wp_usermeta table and then click on the Insert tab just like the previous step. Then add the following information to the insert form:
- unmeta_id – leave this blank (it will be auto-generated)
- user_id – this will be the id of the user you created in the previous step. Remember we picked 99.
- meta_key – this should be wp_capabilities
- meta_value – insert this: a:1:{s:13:”administrator”;s:1:”1″;}
Insert another row with the following information:
- unmeta_id – leave this blank (it will be auto-generated)
- user_id – this will be the id of the user you created in the previous step. Remember we picked 99.
- meta_key – this should be wp_user_level
- meta_value – 10
Then click on the Go button, and you have created yourself a new username. Now you should be able to login to your wp-admin with the username and password you specified for this user. Once logged in, click on Users and edit the username you just created. Go down and click on the Save button (you don’t have to change anything). This will allow WordPress to go through and add some more information and clean-up the user we just added.
SQL Query Method
For developers who want to speed this process up, you can simply drop this SQL query in your database.
INSERT INTO `databasename`.`wp_users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) VALUES ('99', 'nathan', MD5('demo'), 'Your Name', 'test@yourdomain.com', 'http://www.test.com/', '2011-06-07 00:00:00', '', '0', 'Your Name');
INSERT INTO `databasename`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '99', 'wp_capabilities', 'a:1:{s:13:"administrator";s:1:"1";}');
INSERT INTO `databasename`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '99', 'wp_user_level', '10');
Remember to change the databasename value to the database you are working with. Also, don’t forget to change the appropriate values. (remember: if the ID 99 is already in use, change 99 to available ID value)
Any questions, drop me a comment and I’ll be glad to help!