23 March 2022

If needed, you can manually add a WordPress user to your website’s database. That is not how you would normally create users – you can simply add users via the WordPress dashboard. However, if your user account has somehow been damaged or deleted then you can still access the dashboard by manually creating a new user.

Adding a WordPress user via WP-CLI

As always, there is more than one way to create a new WordPress user. The easiest way to add a new user is via WP-CLI. If you have access to WP-CLI then you can create a new user with a single command:

$ wp user create heironymus hiero@example.com --role=administrator
Success: Created user 3.
Password: 7KdDyJZSMdjfjoi989*kj7J

Here, I created a user name heironymus. The user’s email address is hiero@example.com and I made the user an administrator. Of course, the new user doesn’t have to be an administrator. You can give the user any existing role. Run the command wp role list if you want to check the available roles.

Adding a new user via the database

If you don’t have access to WP-CLI then you can instead run three SQL commands. The first command creates the user, and the two other commands set the user’s privileges. I will use phpMyAdmin to illustrate how that works, but you can use whichever tool you prefer. As long as you are able to enter SQL commands you are good to go.

The basic user details, such as the login name and email address, are stored in the users table. If the table prefix for your WordPress database is wp_ then you can list all the fields in the users table with the command DESCRIBE wp_users.

You can view tables via phpMyAdmin, which is a graphical tool for working with databases. The image shows the structure of a WordPress users table. It shows all fields in the table, such as ID and user_login, as well as data about the fields (such as the field types).
Image: The wp_users table.

Oddly, none of the fields are required. For instance, you can run a nonsensical query such as INSERT INTO wp_users (user_email) VALUES ('test@texample.com'). That will insert a user with just a user ID (which is added automatically) and email address. You don’t want to do that, of course. However, it does mean that you don’t have to populate all ten fields.

Creating the user

As mentioned, we first need to add a user to the users table. To do so you first need to select the database. In phpMyAdmin you can simply click on the name of the database in the left-hand pane. You can then select the SQL tab to enter SQL commands. If you are instead adding a user via the command line then you can use the USE command to select your database. For instance, my database is named example_db. To select the database on the MySQL/MariaDB command line I can therefore enter USE example_db;.

phpMyAdmin lets you enter SQL command. The image shows the long command needed to add a new user to the users table. The command can be executed by clicking on a button named Go.
Image: inserting a new user via phpMyAdmin. Note that I have selected the wp_users table in the left hand pane. To enter commands you can then select the SQL tab.

The INSERT query shown in the image is as follows:

INSERT INTO wp_users (ID, user_login, user_pass, user_nicename, user_email, user_registered, user_status, display_name)
VALUES ('catalyst2', MD5('VjJn_EKcK56RCn_mshwY'), 'catalyst2', 'support@catalyst2.com', NOW(), '0', 'catalyst2');

Please don’t blindly copy and paste the query! My database uses the table prefix wp_, and the name of the users table is therefore wp_users. Your database might have a different table prefix, so you may need to change that. Also, please change the login and user names (catalyst2), the password and the email address to something else as well.

Setting the privileges

Next you need to run two queries on the usermeta table. It is very important that you enter the correct value in the user_id field. The value has to be the ID of the user you just created. In my case the ID is 3 but it is likely to be something else for you. So, check the ID in the users table. Alternatively, you can also run a SELECT query to get the ID. My new user’s login name is catalyst2, and I can therefore get the ID for that user like so:

SELECT ID FROM wp_users WHERE user_login = 'catalyst2';

And, again, before you copy and paste the below commands, remember to change the user_id and to check the prefix (which is my case is wp_):

INSERT INTO wp_usermeta (umeta_id, user_id, meta_key, meta_value) VALUES (NULL, '3', 'wp_capabilities', 'a:1:{s:13:"administrator";s:1:"1";}');
INSERT INTO wp_usermeta (umeta_id, user_id, meta_key, meta_value) VALUES (NULL, '3', 'wp_user_level', '10');

And that’s it – you should now be able to log in as the user.