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

Manually Adding WordPress Admin to the Database Using SQL Queries

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

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:

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@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).
  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.


56 thoughts on “How to Manually Add a WordPress Administrator to the Database using SQL Queries

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

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

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

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

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

  4. 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!

  5. 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!

  6. 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!

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

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

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

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

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

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

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

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

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

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

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

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

  14. 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?

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

  15. 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!

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

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

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

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

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

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

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

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

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

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

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

  19. Dear Robert

    I am getting a

    -bash: syntax error near unexpected token `(‘

    when i run the command. Any particular reason?

    Regards

    Jana

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


Leave a Reply

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

Stay in the loop

Subscribe to the Melapress newsletter and receive curated WordPress management and security tips and content.

Newsletter icon

It’s free and you can unsubscribe whenever you want. Check our blog for a taste.

Envelope icon
newsletter-pop-up