How to create a WordPress admin user from phpMyAdmin (safely and reusably)

Sometimes, while migrating or recovering a WordPress site, you may need to create an administrator user manually, directly from phpMyAdmin. This happens, for example, when the login credentials are lost, or when the site is moved and the users are not migrated correctly.

In this guide I show you how to create an admin user via SQL, with a classic method and a more advanced, reusable version that comes in handy if you often work on several WordPress installations.

When do you need this method?

• You have lost access to the WordPress dashboard
• The site has been migrated and the admin account no longer works
• You are working on a local or staging site and want quick administrator access
• You are building a site for a client and want to create your own technical user

Quick method: create an admin via SQL

You can create a user directly from phpMyAdmin with a simple SQL query. Make sure you adjust the table prefix if it is not wp_.

INSERT INTO `wp_users` (
  `user_login`,
  `user_pass`,
  `user_nicename`,
  `user_email`,
  `user_status`,
  `display_name`,
  `user_registered`
)
VALUES (
  'adminuser',
  MD5('SecureP@ss123'),
  'Admin User',
  'admin@example.com',
  0,
  'Admin User',
  NOW()
);
SET @user_id = LAST_INSERT_ID();
INSERT INTO `wp_usermeta` (`user_id`, `meta_key`, `meta_value`) VALUES
(@user_id, 'wp_capabilities', 'a:1:{s:13:"administrator";b:1;}'),
(@user_id, 'wp_user_level', '10');

Warning: change the wp_ prefix if your database uses a different one (e.g. wp5d_).

Reusable method: a dynamic SQL script

If you work with WordPress often, a reusable SQL script where you only change the variables at the top can save time.

-- Change these values
SET @username = 'adminuser';
SET @password = 'SecureP@ss123';
SET @email    = 'admin@example.com';
SET @display  = 'Admin User';
SET @prefix   = 'wp';
-- Create the new user
SET @insert_user_sql = CONCAT('
INSERT INTO `', @prefix, '_users` (
  `user_login`, `user_pass`, `user_nicename`,
  `user_email`, `user_status`, `display_name`, `user_registered`
)
VALUES (
  ''', @username, ''',
  MD5(''', @password, '''),
  ''', @display, ''',
  ''', @email, ''',
  0,
  ''', @display, ''',
  NOW()
);');
PREPARE stmt FROM @insert_user_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- Get the user ID
SET @user_id = LAST_INSERT_ID();
-- Assign the administrator role
SET @insert_meta_sql = CONCAT('
INSERT INTO `', @prefix, '_usermeta` (`user_id`, `meta_key`, `meta_value`) VALUES
(', @user_id, ', ''', @prefix, '_capabilities'', ''a:1:{s:13:"administrator";b:1;}''),
(', @user_id, ', ''', @prefix, '_user_level'', ''10'');');
PREPARE stmt2 FROM @insert_meta_sql; EXECUTE stmt2; DEALLOCATE PREPARE stmt2;

Logging in to the site

Once the script has run, you can log in at /wp-login.php with:

• Username: the one you set in the @username variable
• Password: the one you set (stored as an MD5 hash, which WordPress accepts at login and upgrades automatically)
• Role: Administrator

Useful downloads

[download id=”25576″]