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
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| UPDATE wp_users SET user_pass =md5('newpassword') WHERE user_login = 'yourusername'); | |
| UPDATE myprefix_users SET user_pass =md5('newpassword') WHERE user_login = 'yourusername'); |
Delete Orphaned User Meta
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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'); |
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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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', '[email protected]', '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', '[email protected]', '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'); |
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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
What does SQL stand for? (Yes I am in trouble & know nothing) Ha, Ha, Ha……..a chance to learn!!!!! I hired a person to do my site & it has never worked right, never finished…..Just kept trying to Jam me………..I have learned alot……..but sometimes I make it worse when I try to fix the broken links and things that dont work. 🙁
SQL means Structured Query Language. In other words, the programming that fetches the information from a database. In WordPress all your information sits in a database and WordPress is simply the GUI (Graphical User Interface) that helps you interact and properly store that information in the database while also displaying the information to the external user in a neat way (the PHP side of things). PHP means Personal Home Page and is server-side scripting. Simply, it is what the server serves the browser to display. JavaScript, in web development, is primarily client-side scripting, which means that it is programming that doesn’t run until the browser reads it (though there are JavaScript implementations than can run server-side.).