UPDATE wp_users SET user_pass =md5('newpassword') WHERE user_login = 'yourusername');

UPDATE myprefix_users SET user_pass =md5('newpassword') WHERE user_login = 'yourusername');

UPDATE wp_users SET user_login = 'NewName' WHERE user_login = 'Admin';

UPDATE myprefix_users SET user_login = 'NewName' WHERE user_login = 'Admin';

POPULAR WORDPRESS SQL SCRIPTS: USER ADMINISTRATION

FEBRUARY 15, 2014 BY TRAVIS SMITH2 COMMENTS

Here are some of the most common WordPress SQL user administration scripts that I run.

Backup! Backup! Backup!

First and foremost, before manipulating the database, ALWAYS back it up!

I am going to assume that you have access via a WordPress SQL Plugin or phpMyAdmin.

Remember, if you are like me and change the prefix, be sure to change the wp_ prefixes below to whatever your prefix is. I have added myprefix_ to help demonstrate where this change would be.

Change Default Administrator Name

12	UPDATE wp_users SET user_login = 'NewName' WHERE user_login = 'Admin'; UPDATE myprefix_users SET user_login = 'NewName' WHERE user_login = 'Admin';

view rawdefault-admin-name.sql hosted with ❤ by GitHub

Reset User Password

12	UPDATE wp_users SET user_pass =md5('newpassword') WHERE user_login = 'yourusername'); UPDATE myprefix_users SET user_pass =md5('newpassword') WHERE user_login = 'yourusername');

view rawuser-password.sql hosted with ❤ by GitHub

Delete Orphaned User Meta

12	DELETE FROM wp_usermeta WHERE user_id NOT IN (SELECT ID FROM wp_users) DELETE FROM myprefix_usermeta WHERE user_id NOT IN (SELECT ID FROM myprefix_users)

view raworphaned-wpusermeta.sql hosted with ❤ by GitHub

Replace User Meta

12	UPDATE wp_usermeta SET meta_key = REPLACE (meta_key, 'old_name', 'new_name'); UPDATE myprefix_usermeta SET meta_key = REPLACE (meta_key, 'old_name', 'new_name');

view rawreplaceMetaKey.sql hosted with ❤ by GitHub

Add New Admin User

In the example below, I am using an ID of 2. Change this number to the next user available, or some safe high number.

1234567

INSERT INTO databasename.wp_users (ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name) VALUES ('2', 'admin_demo', MD5('demo_password'), 'Travis Smith', 'no-reply@wpsmith.net', 'http://wpsmith.net/', '2014-02-15 00:00:00', '', '0', 'Travis Smith'); INSERT INTO databasename.wp_usermeta (umeta_id, user_id, meta_key, meta_value) VALUES (NULL, '2', 'wp_capabilities', 'a:1:{s:13:"administrator";s:1:"1";}'); INSERT INTO databasename.wp_usermeta (umeta_id, user_id, meta_key, meta_value) VALUES (NULL, '2', 'wp_user_level', '10');   INSERT INTO databasename.myprefix_users (ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name) VALUES ('2', 'admin_demo', MD5('demo_password'), 'Travis Smith', 'no-reply@wpsmith.net', 'http://wpsmith.net/', '2014-02-15 00:00:00', '', '0', 'Travis Smith'); INSERT INTO databasename.myprefix_usermeta (umeta_id, user_id, meta_key, meta_value) VALUES (NULL, '2', 'myprefix_capabilities', 'a:1:{s:13:"administrator";s:1:"1";}'); INSERT INTO databasename.myprefix_usermeta (umeta_id, user_id, meta_key, meta_value) VALUES (NULL, '2', 'myprefix_user_level', '10');

view rawnew-admin.sql hosted with ❤ by GitHub

Delete Unknown Users

This will delete users not found in comments or as an author of a post. Do not use this script for community sites or sites with Subscribers.

1234567

DELETE FROM wp_users WHERE ID > 1 AND ID NOT IN (SELECT DISTINCT post_author FROM wp_posts) AND ID NOT IN (SELECT DISTINCT user_id FROM wp_comments); DELETE FROM wp_usermeta WHERE user_id > 1 AND user_id NOT IN (SELECT DISTINCT post_author FROM wp_posts) AND user_id NOT IN (SELECT DISTINCT user_id FROM wp_comments); DELETE FROM wp_links WHERE link_owner > 1 AND link_owner NOT IN (SELECT DISTINCT post_author FROM wp_posts) AND link_owner NOT IN (SELECT DISTINCT user_id FROM wp_comments);   DELETE FROM myprefix_users WHERE ID > 1 AND ID NOT IN (SELECT DISTINCT post_author FROM myprefix_posts) AND ID NOT IN (SELECT DISTINCT user_id FROM myprefix_comments); DELETE FROM myprefix_usermeta WHERE user_id > 1 AND user_id NOT IN (SELECT DISTINCT post_author FROM myprefix_posts) AND user_id NOT IN (SELECT DISTINCT user_id FROM myprefix_comments); DELETE FROM myprefix_links WHERE link_owner > 1 AND link_owner NOT IN (SELECT DISTINCT post_author FROM myprefix_posts) AND link_owner NOT IN (SELECT DISTINCT user_id FROM myprefix_comments);

view rawdelete-unknown-users.sql hosted with ❤ by GitHub

Get All Admins

123

SELECT u.ID, u.user_login, u.user_nicename, u.user_email FROM wp_users u INNER JOIN wp_usermeta m ON m.user_id = u.ID WHERE m.meta_key = 'wp_capabilities' AND m.meta_value LIKE '%admin%' ORDER BY u.user_registered   SELECT u.ID, u.user_login, u.user_nicename, u.user_email FROM myprefix_users u INNER JOIN myprefix_usermeta m ON m.user_id = u.ID WHERE m.meta_key = 'myprefix_capabilities' AND m.meta_value LIKE '%admin%' ORDER BY u.user_registered

view rawget-admins.sql hosted with ❤ by GitHub

Share this:

• Twitter2
• Google
• Facebook
• StumbleUpon
• Reddit
• Pinterest
• LinkedIn1
• Pocket
• Print

Like this:

FILED UNDER: SNIPPETS TAGGED WITH: SQL, USER

About Travis Smith

As a WordPress Enthusiast, Travis writes about his journey in WordPress trying to help other WordPress travelers and enthusiasts with tutorials, explanations, & demonstrations of the things he learns.