Manually Add a WordPress User with Administrator Role to the MySQL Database using SQL Quries or phpMyAdmin
In a recent WordPress hack attack which we worked on and recovered, the owner’s WordPress administrator account was demoted to a user role, therefore the owner did not have any control over the WordPress installation. To regain back access to WordPress, we manually created a new WordPress user with an Administrator role directly in the database.
In this tutorial we will show you how to manually create a WordPress administrator in the WordPress database by using any of the following methods; MySQL command line (SQL queries), or via phpMyAdmin.
Create a WordPress User using SQL Queries
Or as frequently referred to, MySQL Command Line
If you have access to your MySQL database server via command line, you can use the below SQL queries to create a new WordPress administrator in the database.
INSERT INTO `wordpressdatabase`.`wp_users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_status`, `display_name`) VALUES ('1000', 'tempuser', MD5('Str0ngPa55!'), 'tempuser', 'support@melapress.com', '0', 'Temp User');
INSERT INTO ` wordpressdatabase`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '1000', 'wp_capabilities', 'a:1:{s:13:"administrator";b:1;}');
INSERT INTO ` wordpressdatabase`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '1000', 'wp_user_level', '10');
The above SQL SQL queries will create a new WordPress administrators with the following details:
- User Id: 1000
- Username: tempuser
- Password: Str0ngPa55!
- Email: support@melapress.com
Before using the above MySQL queries do not forget to:
- change the wordpressdatabase to the WordPress database you are working with
- change the default table prefix (wp_) if the WordPress database you are working with have non default prefixes
- change the prefix of the entries wp_capabilities and wp_user_level if you have configured non default prefixes
- change the user Id to a bigger number if you have created more than 1000 WordPress users (If you do not specify a user ID it will be automatically generated. Then retrieve the record using an SELECT SQL statement).
Once the above SQL queries are executed, you can login to your WordPress blog or website with the newly created WordPress administrator account.
Create WordPress Administrator with phpMyAdmin
To create a new WordPress user with an administrator role directly in the database using the web based phpMyAdmin, first login to phpMyAdmin and click the WordPress database. Then:
Modify the wp_users Table
- Click on wp_users table and click the Insert tab as seen in the below screenshot.
- Populate the temp administrator information as seen in the above screenshot, mainly:
- ID: 1000 (You can pick any number. We choose 1000 in case the WordPress installation already contains a lot of users)
- user_login: tempuser (the username used to login to WordPress)
- user_pass: Str0ngPa55! (The user password. Make sure you select MD5 from the functions drop down menu)
- user_nicename: temp user (The nickname required by WordPress)
- user_email: support@melapress.com (The user’s email address required by WordPress)
- user_registered: configure it to the current date
- user_status: 0
- display_name: Temp User (The display name for the user, i.e. how other users will see it).
- Once ready click the Go button to insert the values into the database.
Modify the wp_usermeta Table
- Click on wp_usermeta table and click the Insert tab as seen in the below screenshot.
- Populate the fields with the below details as seen in the above screenshot:
- umeta_id: leave this blank (value will be auto generated)
- user_id: the user ID of the user you created in the previous step. In our example we used 1000.
- Meta_key: wp_capabilities
- meta_value: a:1:{s:13:”administrator”;b:1;}
- Once ready click the Go button to insert the values into the database.
- Create another row by clicking again the Insert tab and populate the fields with the below details, as seen in the below screenshot:
- umeta_id: leave this blank (value will be auto generated)
- user_id: the user ID of the user you created in the previous step. In our example we used 1000.
- Meta_key: wp_user_level
- meta_value: 10
- Once ready click the Go button to insert the values into the database.
The new WordPress user with administrator role is created so proceed to login to your WordPress blog or website using the newly created account.
Melapress Tip: Use the newly created user until you fix your issues and regain access to the previous account. Delete it once ready. We recommend you to create a new WordPress administrator account using a strong username and a strong password.
Dear Robert
I am getting a
-bash: syntax error near unexpected token `(‘
when i run the command. Any particular reason?
Regards
Jana
Hello Jana, it seems that you are running these commands in the command prompt, on the OS. These are MySQL queries, so you need to connect to the MySQL server and then run these queries.
Somewhat belated for 7 years but I thought I would post this. Here are some sample queries where if you have another table with the users you need to create. Replace the table names where necessary. The reason I used the ID > value is in case it breaks the insert from the bulk and you need to pick up where you left off. Just query the last value in the wp_users table and increment by +1 because those records have not been inserted yet. I hope this helps someone.
INSERT INTO wp_users ( user_pass, user_login, user_nicename, user_email, display_name)SELECT MD5(lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0)) as user_pass, Email as user_login, Name as user_nicename, Email as user_email, Name as display_name FROM business_dir WHERE Email NOT IN(SELECT user_email FROM wp_users)
INSERT INTO wp_usermeta ( user_id, meta_key, meta_value)select ID as user_id, ‘wp_capabilities’ as meta_key, ‘a:1:{s:13:”subscriber”;b:1;}’ as meta_value FROM wp_users where ID > 65728
INSERT INTO wp_usermeta (user_id, meta_key, meta_value)select ID as user_id, ‘wp_user_level’ as meta_key, ‘0’ as meta_value FROM wp_users where ID > 65728
Thanks a lot Oliver. You made my day by solving my prefix issue. It will be the same prefix as the database prefix.
Is there a way I can add a WordPress user to multiple WP sites on the same server using 1 SQL query?
Hello Ryan,
Do you have a multisite or multiple WordPress websites running on the same server? In case of multisite you can. In case of multiple websites, you cannot since you are dealing with different databases.
Thanks for replying Robert. I got your query to work on one database as a test. I’m adding Editors so I had to adjust some things in the second two queries for that.
BUT – I do have root access to PHPmyadmin so I can see all tables. But what is the command to INSERT INTO all tables at once?
To answer the question, it’s multiple sites on one server. See my clarifying question above. Do I need to run it on all individual databases? I did find a command I can run in SSH on root to accomplish this but a little scare to do it on 100+ sites.
If you want you can share the query with us via email and we will take a look at it for you. Contact me on robert@melapress.com
hi,
i can not access wordpress…invalid password, i changed password from myphp ,generated with md5, go…it does not work…keeps saying invalid password. I want to mention that i am running wordpress locally with WAMP.thank u
Hello Linda, in such case simple follow the procedure documented in Reset WordPress password from phpMyAdmin.
Hey! Help me, my PHP not show this table – Have any idea why?
Hello Rony,
Drop us an email on support@melapress.com and we will help you get sorted.
Thanks Mr.Robert Abela for a great article. My issues was resolved successfully. Cheers!
Thanks for the scripts and info – helpful in not having to figure it all out for myself (had to help support a WP install that I didn’t setup, and for which I did not have an admin login).
Just a suggestion for your SQL solution – rather than hard-coding the ID value of the user (e.g. “1000”), which assumes that you’ve researched what value you can properly use / made sure there aren’t already 1000 users registered – you can use a nested query to dynamically populate the latter 2 queries with the proper ID. That way you always get the correct ID throughout the process, and you can let MySQL worry about what that value should be.
So:
=================
INSERT INTO `wordpressdatabase`.`wp_users` (`user_login`, `user_pass`, `user_nicename`, `user_email`, `user_status`, `display_name`) VALUES (‘tempuser’, MD5(‘Str0ngPa55!’), ‘tempuser’, ‘support@melapress.com’, ‘0’, ‘Temp User’);
INSERT INTO ` wordpressdatabase`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, (SELECT ID FROM wp_users WHERE user_login = ‘tempuser’), ‘wp_capabilities’, ‘a:1:{s:13:”administrator”;b:1;}’);
INSERT INTO ` wordpressdatabase`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, (SELECT ID FROM wp_users WHERE user_login = ‘tempuser’), ‘wp_user_level’, ’10’);
=================
One addendum thought: I’d probably also suggest always running a quick check to make sure that whatever username is being inserted doesn’t already exist in the system – i.e.:
=================
SELECT user_login, user_email FROM `wordpressdatabase`.`wp_users` WHERE `user_login` = “tempuser”;
=================
To make sure you’re not going to create a double user conflict, or improperly promote an existing user’s privileges to admin levels.
Hi Nathan,
Thanks a lot for your suggestions, they do in fact make a lot of sense. To be honest though I didn’t mention them because if you have access to run queries on the database most probably you already have access to see the data, hence you can see for yourself which ID is available etc. Though again, good thought automating them.
Hi!
Thanks for your hints.
I got locked out of my site and was unable to solve it on my own.
Then I stumbled on this article and the last thing I wasn’t checking was wp_capabilities key.
Somehow my admin role was changed after plugin updates and it got me locked out of my dashboard…
Thanks!
Hi Darko,
Glad our article was of help. If you updated a plugin and you got locked out, you should look into such issue. This is not something that should happen, so keep an eye on it.
Hi Richard,
Good catch there. Sorry for the delay in replying had to do some serious research before I found the answer.
As you highlighted both have the same end result but one value is boolean and another one is string. There were some early versions of WordPress that were using strings instead of boolean and because I was doing testing on a test site that have been updated from very old versions I used such values. Having said that now WordPress just uses boolean so stick to boolean values.
I also updated the queries and the article so everything is using boolean now. Thanks for your feedback and for following our blogs.
The tutorial has been very useful, and rescued a couple of sites.
Hi Robert
In the text for meta_value you show
a:1:{s:13:”administrator”;s:1:”1″;}
Alvise suggested the _last_ s should be b like this
a:1:{s:13:”administrator”;b:1;}
and your reply to Natasa uses b (boolean) instead of s(string):
a:1:{s:10:”subscriber”;b:1;}
Are both types equivalent in this case? Which is preferred?
Hi Richard,
Sorry for the delayed response, been very busy lately with the plugins 🙂
Off hand I do not have an answer for you but will look further into this and get back to you. In the meantime if you find something before I do, please update me. Thanks a lot and keep in touch.
What about creating a common user (wp_user_level = 0) ? How does the wp_capabilities json will look like?
Hi Alison,
It should look like this: a:1:{s:10:”subscriber”;b:1;}
I trust the above answers your query.
My website’s admin panel does not connect on the server , when i try to connect reload the page and goto the index page of the website.
tnx in advance
HI Dolar,
Unfortunately I cannot understand your problem. Can you be more specific please?
In my case when i create user with admin privileges with help of mysql and check back he user levels changes to subscriber level itself.
HI Knm,
Never seen this happening to be honest. Just in case can you confirm that every user session is logged out at the moment you are trying to create the admin account via MySQL?
Thanks for the great tutorial. I do have a problem though: after completing all the phpmyadmin version steps, after I successfully login I get this warning:You do not have sufficient permissions to access this page.
It seems you might have entered something incorrect. Can you double check? Else send us an email on support@melapress.com and we can check for you.
Hi Robert,
I followed the command lines and created 2 tempusers trying to gain back access as administratior, but I still get the error, while I try to access dashboard. “You have not the right permissions to access this page” – translated from german into english.
Could you please give me further instructions?
Best
Alex
I fixed it, had to change this value and did not matched in the first try:
UPDATE `Database`.`Prefix_usermeta` SET `meta_key` = ‘Prefix_capabilities’ WHERE `Prefix_usermeta`.`umeta_id` =NEW ID OF TEMPUSER;
Glad you fixed it. Should you have any queries do not hesitate to get in touch. Have a great weekend 🙂
I did exactly what you said, but it keeps saying the password for my user is wrong…it is not wrong, it can’t be I’ve checked in PHPMyAdmin so many times!
What to do, going mental here.
Hi Will,
What is the exact error; incorrect password or incorrect credentials? The more details you have about the error message the more help we can provide you with. Feel free to drop us an email on support@melapress.com if you haven’t solved the issue yet.
Hi Robert, you have saved my life. A friend of mine had his WordPress site suspended and could not remember any of the user names, passwords, as well as losing access to all of the email accounts that he used to setup the site.
Just as well that he had not lost the access to his host. With that, you tutorial and a bit of my help he is now up and running again.
Thanks.
HI James,
Thanks for following us and you are welcome. Glad our article was useful.
Hi Tom,
No the administrator will not be notified via email that a new user is created since it was created manually via the database and the notification is a WordPress feature.
what about this too?
If I change the current administrator user’s email and password then login WordPress with the new password, user and email I created and fix whatever then return everything to what it was would the main administrator user that was firstly created when installing WordPress be notified?
Thanks!
Hi Tom,
Can you please let us know what you are trying to do though exactly? I mean what you want to achieve? This can help us determine an answer for you.
I just came through this as I was reading about WordPress security. I’m more interested in knowing if this could happen or not for my general knowledge. The second is that I have my password of my host shared with some support and editing people and I’ve given them different roles, however, they do have the password to access server i.e. myadminphp etc.. So just wondering if they for example could access website using my name as admin then change back to what it was without me being notified about this!
Thanks heaps!
If they have access to the server (phpmyadmin) they can do everything, i.e. they can even reset your password and then change it back, they can create new users etc. All of such information is stored in the database, hence if someone has access to the database, then they can virtually do everything.
Hi guys,
Thanks for sharing
If I do this and add a new user will the main administrator user that was firstly created when installing WordPress be notified on their specified email that a new user has been added?
Thanks!
Hi!
I tried but I always get a warning that I don’t have permissions..
A question. Should I use in wp_capabilities a:1:{s:13:”administrator”;s:1:”1″;} or a:1:{s:13:”administrator”;b:1;} ?
Thanks!
Hi Alvise,
Sorry for the late reply. As regards the warning, you mean is it generated from phpmyadmin? Send us an email on support@melapress.com and we can look into it if you want.
Looking forward to hearing from you.
Hello,
When I’m trying to login with the new user I created, I get a blank page with a message: “The requested page not found”. What am I doing wrong?
Thanks!
Best Regards,
Amir
Hi Amir,
Thank you for getting in touch. Are you creating a WordPress user manually or via the dashboard? The more details we can get the easier it would be to troubleshoot this issue. I’d recommend you to drop us an email on support@melapress.com so we can look into your issue.
Have a great day.
Are you talking about table names or about the value in field meta_key?
Hi Heinz,
I’m sorry but I did not understand your question. Can you please explain further to which section of the article you are referring?
Thanks Robert for this carefully written Tutorial – works fine! (And thanks to the Comments, it really needs to be a ” not ” or ″. To be sure, simply copy and paste it from other wp_capabilities fields.)
Another one: note that wp_capabilities and wp_user_level needs to be renamed if your WordPress-Prefix isn’t wp_ .
Hi Oliver,
Thanks for your comments and glad you liked the tutorial. For sure it does work 🙂 When we write such tutorials, we always test it out ourselves first.
P.S. we mentioned that the prefix of the tables should be changed should your prefixes be different in the beginning of the article 🙂
Hey Oliver,
Man, thank you for your comment. Changing to my custom prefix solved my problem finally.
Yippie.
Constantin
If you get the message ‘You do not have sufficient permissions to access this page.’ when logging in, check the meta_value on step 2: make sure the quotation marks are ” as opposed to the curly ones that look like ” .
Hi Joshua,
Very good point indeed.
Oh, god. I spent hours trying to figure this out. LOOK OUT FOR CURLY QUOTES. They’re always the answer to the problem.
Thanks for the post, and thanks for the comment Joshua, really helpful