How to Manually Add a WordPress Administrator to the Database using SQL Queries

Last updated on October 18th, 2014 by Robert Abela. Filed under WordPress Security Tutorials

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@wpwhitesecurity.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:

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

  1. Click on wp_users table and click the Insert tab as seen in the below screenshot.

Manually insert details in the wp_user WordPress database table to create a new WordPress administrator account using phpMyAdmin web based tool

  1. 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@wpwhitesecurity.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).
  2. Once ready click the Go button to insert the values into the database.

Modify the wp_usermeta Table

  1. Click on wp_usermeta table and click the Insert tab as seen in the below screenshot.

Configuring the wp_usernmeta WordPress database table manually with phpMyAdmin

  1. 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;}
  2. Once ready click the Go button to insert the values into the database.
  3. 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

Manually insert details in the wp_usermeta WordPress database table to create the second row when creating a new WordPress user account with administrator role using phpMyAdmin web based tool

  1. 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.

WP White Security 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.

WordPress Hosting, Firewall and Backup

WP White Security is hosted on A2 Hosting, protected with BBQ:Block Bad Queries Firewall and backed up with BlogVault online WordPress backup service

53 comments

Joshua 20/10/2013

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 ” .

Robert Abela 25/10/2013

Hi Joshua,

Very good point indeed.

B 11/12/2013

Oh, god. I spent hours trying to figure this out. LOOK OUT FOR CURLY QUOTES. They’re always the answer to the problem.

Guillermo 25/09/2018

Thanks for the post, and thanks for the comment Joshua, really helpful

Oliver Hutz 13/12/2013

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_ .

Robert Abela 14/12/2013

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 🙂

Constantin 03/11/2014

Hey Oliver,

Man, thank you for your comment. Changing to my custom prefix solved my problem finally.

Yippie.
Constantin

kamal thakur 30/11/2016

Thanks a lot Oliver. You made my day by solving my prefix issue. It will be the same prefix as the database prefix.

Heinz 09/01/2014

Are you talking about table names or about the value in field meta_key?

Robert Abela 09/01/2014

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?

Amir Weinberger 08/02/2014

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

Robert Abela 18/02/2014

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@wpwhitesecurity.com so we can look into your issue.

Have a great day.

Alvise 20/02/2014

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!

Robert Abela 25/02/2014

Hi Alvise,

Sorry for the late reply. As regards the warning, you mean is it generated from phpmyadmin? Send us an email on support@wpwhitesecurity.com and we can look into it if you want.

Looking forward to hearing from you.

Tom 26/02/2014

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!

Robert Abela 26/02/2014

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.

Tom 26/02/2014

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!

Robert Abela 26/02/2014

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.

Tom 27/02/2014

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!

Robert Abela 05/03/2014

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.

James 07/03/2014

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.

Robert Abela 12/03/2014

HI James,

Thanks for following us and you are welcome. Glad our article was useful.

Will 27/04/2014

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.

Robert Abela 02/05/2014

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@wpwhitesecurity.com if you haven’t solved the issue yet.

Alex 24/05/2014

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

Alex 24/05/2014

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;

Robert Abela 25/05/2014

Glad you fixed it. Should you have any queries do not hesitate to get in touch. Have a great weekend 🙂

Val 26/05/2014

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.

Robert Abela 27/05/2014

It seems you might have entered something incorrect. Can you double check? Else send us an email on support@wpwhitesecurity.com and we can check for you.

knm 05/06/2014

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.

Robert Abela 08/06/2014

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?

Dolar Patel 12/06/2014

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

Robert Abela 15/06/2014

HI Dolar,

Unfortunately I cannot understand your problem. Can you be more specific please?

Natasa 17/07/2014

What about creating a common user (wp_user_level = 0) ? How does the wp_capabilities json will look like?

Robert Abela 22/07/2014

Hi Alison,

It should look like this: a:1:{s:10:”subscriber”;b:1;}

I trust the above answers your query.

Richard 13/08/2014

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?

Robert Abela 19/08/2014

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.

Robert Abela 27/09/2014

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.

Darko 28/08/2014

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!

Robert Abela 03/09/2014

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.

Nathan 17/10/2014

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@wpwhitesecurity.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’);

=================

Nathan 17/10/2014

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.

Robert Abela 20/10/2014

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.

Shafii 23/07/2015

Thanks Mr.Robert Abela for a great article. My issues was resolved successfully. Cheers!

Rony 30/09/2015

Hey! Help me, my PHP not show this table – http://www.wpwhitesecurity.com/wp-content/uploads/2013/10/wp_users_table.png – Have any idea why?

Robert Abela 08/10/2015

Hello Rony,

Drop us an email on support@wpwhitesecurity.com and we will help you get sorted.

linda 27/10/2015

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

Robert Abela 09/11/2015

Hello Linda, in such case simple follow the procedure documented in Reset WordPress password from phpMyAdmin.

Is there a way I can add a WordPress user to multiple WP sites on the same server using 1 SQL query?

Robert Abela 16/01/2016

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.

Robert Abela 23/02/2016

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@wpwhitesecurity.com

Leave a Reply

Your email address will not be published. Required fields are marked *