Few days ago, we ran into an issue where a user’s site got hacked and their admin account was deleted from the database. This locked them out of their site without any other entry. We went in to the phpMyAdmin and created a new admin user to grant them access. In this article, we will show you a step by step guide on how to create an admin user in WordPress Database via MySQL.
Note: You should always make a backup of your database before performing any MySQL edits. This tutorial requires basic understanding of how phpMyAdmin works.
First, you need to login to phpMyAdmin and locate your WordPress database. (Below is a screenshot of a HostGator cPanel)
Once you are in, we will be making changes to the wp_users and wp_usermeta tables. Lets go ahead and click on wp_users table.
We need to insert our new admin user’s information, so click on the Insert tab like 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 4).
- 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 4.
- 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 4.
- 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
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 ('4', 'demo', MD5('demo'), 'Syed Balkhi', 'test@yourdomain.com', 'http://www.wpbeginner.com/', '2011-06-07 00:00:00', '', '0', 'Syed Balkhi');
INSERT INTO `databasename`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '4', '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, '4', 'wp_user_level', '10');
Remember to change the databasename to the database you are working with. Also don’t forget to change the appropriate values.
Maybe a weird question, but I followed the steps and can’t login. Also watched for the brackets etc.
So I tried ‘forgot password’ and entered my e-mail which is also in the wp_users table. But it says no user is registered with that e-mail.
Anyone a clue what I missed?
OMG sorry. My fault. I didn’t changed the base URL in the database so my local copy (and login) were pointing at the original site. Feeling stupid.
If you copied the code a:1:{s:13:”administrator”;b:1;} from this blog, it is possible that you got curly quotes instead of regular quotes, which will not work.
There was a problem with the wp_capabilities valve you gave and the problem was with the quotes. the correct one is: wp_capabilities should be a:1:{s:13:”administrator”;s:1:”1″;}
Anyone who wants to solve this problem should copy it exactly as it appears.
I by accident logged in before adding the “usermeta” info. It of course read “You do not have sufficient permissions to access this page.” Even after I added the usermeta info I get the same thing.
Any thoughts on this?
Check to see if the prefix is the same as your main database prefix. We are assuming that the user is using the default wp_ prefix. If your database prefix is something else, then it wouldn’t work.
I’ve tried everything and I still keep getting “You do not have sufficient permissions to access this page.” Any ideas on why this might be happening? Thanks.
Thank you so much this finally allowed me to get into my site. I tried every possible option to reset the admin password but nothing would work. I created the new account and logged in as administrator and was then able to export my work. Thanks again!
Hi, great article, thanks! Have a different issue though, my entire wp_users table got deleted and I don’t know how to create a wp_users table using phpMyAdmin. Can anyone help? Thanks!
Did you try repairing your database using the WordPress repair tool? Maybe that adds it back. Otherwise, you are better off reinstalling WordPress. For future, keep good backups.
using the repairing DB WordPress tool won’t create the wp_users table. What I did is deleting the unused tables from the old one, lowering it’s size and uploading it again. It worked. Thanks.
After attempting to log in it sais..
You do not have sufficient permissions to access this page
why did this happen?
Thanks
This happens if your user permissions level aren’t correct. Can you make sure that the permission level is the same as the other admin user in your phpMyAdmin
I have applied same user level as 10.
But still does not work
Can my developer have any access to my
Cpanel if she doesnt have my ftp or cpanel
Log in details?
I see in phpmyadmin she is listed as admin.
Its all so confusing especially when im new to all
Of this!
Guide is out of date. The correct string for wp_capabilities is now:
a:1:{s:13:”administrator”;s:1:”1″;}
Updated the guide.
Thanks it worked perfectly for me
Thank you. You just saved my arse!!
I echo Ravi’s suggestion to copy the meta value from another admin. In my case “wp_capabilities” was actually databaseName-”_capabilities” and my admin level looked like this: a:2:{s:13:”administrator”;s:1:”1″;s:17:”gform_full_access”;s:1:”1″;}
Thanks for the great article. Saved my day.
Copying of the wp_capabiblities-value from another admin helped me to. In my case the value was a:1:{s:13:”administrator”;s:1:”1″;}. Until this change I had recieved: “You do not have sufficient permissions to access this page.”
Thanks for the useful article.
a:1:{s:13:”administrator”;b:1;} didn’t work for me. I had to copy the meta value from another admin user and then it worked.
For those who encounter “You do not have sufficient permissions to access this page.”, replace “wp_capabilities” with your full wordpress table name, e.g. if your usermeta tables are called wp_yourtableusermeta (as opposed to wp_usermeta in this tutorial), then you need to use wp_yourtablecapabilities instead. Same goes for wp_user_level, change it to wp_yourtableuser_level. Hope this helps.
You do not have sufficient permissions to access this page.
In my opinion tampering with MySQL should not be on wp’beginner’, messing around with the database is simply not a beginner thing.
I don’t know when this was posted, but the time when WordPress used MD5 has long gone. This article should be updated to use wp_hash_password instead.
Hi guys, Anne here, community manager intern at Livefyre. Welcome! Just wanted to drop in and say that everyone here loves this blog; it’s full to the brim with fantastic information. Can’t wait to read more!
I was pretty sure the password was md5 but also using a salt stored in the config file it creates.
Hi,
I hope to get some help here. My blog was hacked with some base64 stuff. I can solve 99% of all the problems but I have 2 Admins in my wp Backend that need to be removed from mysql table before I can take other actions
Sorry, no clue how to track the adin in the table and remove them
WP latest
Access to the mysql database
tks
Kosta
Look in the wp_users table. Delete the users that you don’t want.
If it was salted, just follow the steps, add a real email and have it send you a password reset. Boom.
hope that helped.