Site icon WP Smith

Popular WordPress SQL Scripts: User Administration

Travis Smith

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

UPDATE wp_users SET user_login = 'NewName' WHERE user_login = 'Admin';
UPDATE myprefix_users SET user_login = 'NewName' WHERE user_login = 'Admin';
view raw default-admin-name.sql hosted with ❤ by GitHub

Reset User Password

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 raw user-password.sql hosted with ❤ by GitHub

Delete Orphaned User Meta

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 raw orphaned-wpusermeta.sql hosted with ❤ by GitHub

Replace User Meta

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

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

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 raw delete-unknown-users.sql hosted with ❤ by GitHub

Get All Admins

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 raw get-admins.sql hosted with ❤ by GitHub