Friday, August 15, 2014

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

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';

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)

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);

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

No comments:

Post a Comment