Sometimes, when working with a WordPress multisite network, we may not want to create a super admin user from the dashboard. Instead, we may need to create one directly in the database with SQL queries.
In this tutorial, I will show you how to create an administrator user for WordPress in phpMyAdmin with SQL. We only need two steps:
- create a normal administrator, and
- make that administrator a super admin.
Create an administrator user in phpMyAdmin
First, in the initial SQL query, we create a normal WordPress user.
INSERT INTO wp_users ( user_login, user_pass, user_nicename, user_email, user_url, user_registered, display_name)
VALUES ( 'amos', MD5('myPasswrd'), 'amos', 'no-reply@amos.com', 'https://wphrefs.com', '2023-04-14 09:25:20', 'Amos Lee' );Then we assign the WordPress administrator role to that user.
INSERT INTO wp_usermeta (user_id, meta_key, meta_value)
VALUES ( 2, 'wp_capabilities', 'a:1:{s:13:"administrator";b:1;}' )
Wait. Are we not talking about WordPress multisite? Why are we only using the normal WordPress database tables wp_users and wp_usermeta? If you have that question, I recommend reading my other tutorial about the multisite database structure.
Make the user a super admin of the multisite network
This part is actually a little trickier.
In a multisite network, information about super admins is stored as site meta in the wp_sitemeta table. There is only one option. That option most likely already exists. This means we can use an UPDATE SQL query to replace the existing super admins with our new super admin, like this:
UPDATE wp_sitemeta SET site_admins='a:1:{i:0;s:9:"rudrastyh";}' WHERE site_id=1;Please note that after running this query, all existing super admins will no longer be super admins. So it is better to modify this option carefully by hand. But what exactly is stored in it? What is the value of site_admins?
It is simple. It is just a serialized array of usernames, for example Array( 'rudrastyh' ) or Array( 'misha', 'rudrastyh' ). So you can use the PHP serialize() function or any online tool to convert it into something like a:2:{i:0;s:5:"misha";i:1;s:9:"rudrastyh";}. You can even format it yourself:
a:2:means an array containing 2 elements,i:0;s:5: means the first element of the array is a string with a length of 5, andi:1;s:9: means the second element of the array is a string with a length of 9.
If you are trying to recover or modify a WordPress administrator password, you can also refer to this site’s article about recovering a WordPress password.
