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.
Video Tutorial
If you don’t like the video or need more instructions, then continue reading.
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'), '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, '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.
MKallas says
Thank you. This might come handy in other cases too… I made a Local AMPPS WordPress Install and for some reason first registrant got Subscriber rights instead of Admin. Really interesting. Anyways, PhpMyAdmin and fix of wp_usermeta -> wp_usercapabilities -> “a:1:{s:13:”administrator”;s:1:”1″;}” sorted it out.
Andy says
Hi, thanks for the info. I’ve only been able to login as a general ‘User’ using this method – I can’t see/access the Admin Dashboard. The site is a directory site that i migrated from a backup to a new domain. Any suggestions to get full admin privileges?
B A says
I come here 2-3 times a year to get that nice set of SQL boilerplate. Should probably just save it to my notes at this point. Thanks so much!
WPBeginner Support says
Glad our guide is able to continue to be helpful
Admin
Jana says
Thank you so much for this post! I had so many problems after migrating and changing the base url of my site and this post solved them all.
WPBeginner Support says
Glad our guide was able to help
Admin
Kristen says
I followed these steps and see my new account, but my site still will not allow me to log in. It says invalid user, then if I try resetting the password it says no account exists. What’s my next step to fix this?
WPBeginner Support says
You may want to ensure you added the user to the correct database, you should also be able to reach out to your hosting provider for them to take a look.
Admin
Kyle Eggleston says
Excellent, useful tutorial. Thanks for publishing.
WPBeginner Support says
You’re welcome
Admin
Stefan Pl says
1. Please change the post to include information about table prefixes – a problem that appeared quite a few times in the comments and will keep you from succeeding if you just follow the post and have an alternative prefix.
2. Thanks for the information presented here
WPBeginner Support says
Thank you for your feedback about our post
Admin
Mandy says
For everyone who can login but not see the wp-admin, these instructions aren’t wholly correct.
The wp_cabailities meta_value actually needs to be set as a:1:{s:13:”administrator”;b:1;}
Otherwise your user doesn’t end up with a role set and you can’t see wp-admin.
WPBeginner Support says
Thank you for sharing the code that worked for you
Admin
Jan says
Yes this also worked me, Many Thanks!
Cristina says
Hi. My WordPress website is being managed by a remote developer. He is an admin and so am I. We have a monthly agreement and I have every intention of paying him his dues. What measures can I do to make sure that I keep the access as well as my website should things go sideways? Can you please help me? Thanks in advance.
WPBeginner Support says
Create backups of your site and as long as you are the one paying for hosting you can restore the site
Admin
Janne says
Hi,
Thanks for writing this! I see I am not the only one who was looking for instructions. My user was created, and I can click on the “Reset password” link and also get an e-mail from the system, but I am not able to log in. I saw that my “usermeta” table looks different from the other users’ – mine starts with “closedpostboxes_attachment”, not “first_name” etc. What might be the reason for that?
j
WPBeginner Support says
That table is for meta boxes that you have closed, the order you’re seeing the table shouldn’t cause an issue
Admin
Sofia says
Hi!
I did all the process but when I log in to the dashboard I only see the “Welcome to WordPress” section, I don’t see anything else in the admin area.
Please, can you help me?
WPBeginner Support says
Your hosting provider may have customized their login area, there is normally an advanced section you can go to or reach out to your host and they should be able to assist
Admin
Alexander Mtembenuzeni says
This was really helpful. Thank you!
WPBeginner Support says
Glad our post was helpful
Admin
Anisha says
Working on this for days. Thank you…
WPBeginner Support says
Glad our article could help you
Admin
Glenn Gordon says
I have a WP blog site which I have been locked out of. I created a new admin and can log in, but not to my dashboard. Instead, I go to my main blog page with my name and admin # at the top right and a logout button. How do I gain access to my admin/dashboard area?
WPBeginner Support says
You should be able to add /wp-admin to the end of your domain to get to your admin area but if you have a plugin locking you out, you could disable it using: https://www.wpbeginner.com/plugins/how-to-deactivate-all-plugins-when-not-able-to-access-wp-admin/
Admin
Asif says
Thank you very much!
WPBeginner Support says
You’re welcome
Admin
João says
Hello,
Well, this is embarrassing but… would anyone be able and kind enough to help me solve the father of problems? – wp_users table gone/missing…
Thank you in advance.
WPBeginner Support says
You would want to reach out to your hosting provider if that table is not with your database to see if they have a backup of your database to restore.
Admin
lhoucine says
hi! thank you for your tutorials,
i did all these steps and my issue still the same, when I want to login to my wp-admin, show me this message “Sorry, you are not allowed to access this page.” please if know how to fix this I will so thankful
thank you
WPBeginner Support says
If the user was created, have you tried disabling your plugins in case you have a plugin that could be preventing access? https://www.wpbeginner.com/plugins/how-to-deactivate-all-plugins-when-not-able-to-access-wp-admin/
Admin
Theo says
Make sure you replace wp_capabilities with the correct prefix, if you are using a custom one, for example asdf_capabilities
Holly Fitzgerald says
After backing up my database with updraftPlus, then restoring it, we lost all admin access. I followed these instructions but am still getting an error saying I do not have access. Help!
WPBeginner Support says
You would first want to reach out to updraftplus for your old login, some caching plugins can cause issues with what database your site is attempting to use. If that does not work you can force reset the password for your user using the method in our article here: https://www.wpbeginner.com/beginners-guide/how-to-reset-a-wordpress-password-from-phpmyadmin/
Admin
john juncal says
you are an absolute legend! after3 hours of trying useless suggestion from forums, you came up trumps, thank you!
WPBeginner Support says
Glad our article could help
Admin
stephane says
You Save my Life. Thank you so much!
WPBeginner Support says
Glad our article could be helpful
Admin
Gina says
EXCELLENT tutorial. Thank you so much!
mahmud says
many many thanks bro. you are genius.
Punam says
Many Many thank.. it is very helpful to me and my site work perfectly…
Taieuro says
Thanks your information!
But after inserting a admin accout, i can not login my site.
Appear “Protection” button stop me to login WP dashboard.
Please help me!
WPBeginner Support says
Hi Taieuro,
Please try steps in our WordPress troubleshooting guide, it will help you figure out what’s causing the issue.
Admin
Matthew says
Didn’t work, I’m getting the “Sorry, you are not allowed to access this page.” message. I checked my wp-config.php and the prefix is “wp_” but it still doesn’t work. Any ideas?
Cavid Muradov says
You helped my life, thank you very much.
Julia says
Thank you so much. Worked perfectly. You helped me so much!
Julia
Rivhie says
How did you do it? It didn’t work for me.
Chakerz says
Hi,
change “wp_” to your installation prefix !!
UgoChukwu Agbams says
Thank you very much. This article really helped me a lot, after my admin account was set to customer by a plugin I set to change user role from whatever to customer when they made a purchase.
I never knew it will affect the admin role as well.
JOEL says
Very good, great info. The SQL worked super fast so thumbs up!
kerry says
is there a common reason why user can login but Dashboard is not made available? I see a lot of meta key values in the db
Jules says
Thank you, this has been very helpful.
PM says
Thank you sooooo much for this. Just this morning my wordpress site was hacked and I could not login into wp-admin. It was giving “user not found” error. I used this to fix things in my hostgator account.
Very very grateful. Written instructions are excellent but the video could be slower with more details.
joel says
Hi all,
it didn’t work for me…
an error message tell me that I can’t access to the dashboard
!!HELP !!
Anup Poudel says
It is simple mate.See what is written in you database name.
Like in my case it was wprn_capabilities,wprn_user_level..
James Denning says
I have the same problem. No matter what I do the user does not have any privliges
Shaheen says
I messed around with this more; watched the video several times and it’s still not working.
In my case, I lost admin access after I migrated my WP site from one hosting account to another.
my old admin login is able to login but once in, isn’t able to do anything.
Using mysql, i tried creating a new user and adding the meta data. When I try to login with the new user, it says the password is incorrect. I tried saying “forgot password” and I did not receive any email to the email address i included when making the user with msql.
In mysql i can see my old admin user is still there. I tried adding meta data to give admin privledges to that user, by putting in the corresponding userID. This didn’t change anything. I am still able to login with that user but don’t have access to anything. If anyone has any advice it would be much appreciated!
zohaib says
Thanks a lot, Yes I got my problem solved and this was due to the the role assigned to my user name. Cheers
Bichir Mihail says
Thanks, really helpfull
Rastislav Nichta says
Really very simple and clear guide to how to create a user through MYSQL WordPress database, thank you.
Mary Janis says
This info is outdated cause HG format has all changed. Where the heck is wp_user?
Susan Marshall VA says
Thank you so much for this. Just had a client that locked herself out of her site and I was able to get her back in with these instructions.
Kumar Mritunjay says
I’ve used this on my websites. Works like a charm…very easy to follow!
Thank you for this amazing time saver technique.
sara asadiha says
i did everything u said, but it did not work
what else should i do?
Peter Gough says
Thanks for this. Really helped when I was locked out of WordPress
Michael Sineni says
I have changed my admin access to editor because i was trying to change role capabilities to edit gravity forms. Suddenly i no longer see edit cornerstone or have access!! HELP!
Ayesi says
Really good, everything works perfect, thanks a million!!
Stella says
Thanks a lot! This saved my day. Whew!
Nilo Velez says
I don’t now if it is an update to WordPress data structure, but I’ve just encounter a site in which I’ve had to enter the wp_options meta_key values as:
[table_prefix]capabilities instead of wp_capabilities
[table_prefix]user_level instead of wp_user_level
I leave this here as it might be of help.
Takis Pan says
You sir are awesome!
I guess it uses the database prefix but in case the site has been moved, or someone changed the prefix from “wp_” to something else, capabilities and user_level should be changed accordingly.
Abraham Quilca says
Thanks, this comment helped a lot.
I had to double-check and get the exact table_prefix value from our wp-config.php file. Ours meta_key values were correct except for capitalization. I believe some hosting companies with single-click wordpress installs will set their own table_prefixes, so be sure to check for that as well.
Cheryl says
this worked for me too, thank you Abraham
Loren Helgeson says
I’ve used this on three different websites so far. Works like a charm!
Thank you for this amazing time saver.
Steve Day says
BRILLIANT!!!
Thank you – saved my day