MySQL Drop User

Last updated: October 19, 2026Oct 19, 2026

When an application is retired or a person leaves, their database account should go too. DROP USER deletes an account and all of its privileges in one step. This tutorial shows the syntax, the host detail that trips people up, and what to check before dropping, such as objects that run as that user.

1. Syntax

DROP USER [IF EXISTS] 'user'@'host' [, 'user2'@'host2' ...];

SELECT user, host FROM mysql.user WHERE user NOT LIKE 'mysql.%' AND user <> 'root' ORDER BY user, host;

Accounts before

2. Dropping an Account

DROP USER 'old_app'@'localhost';

Query OK, 0 rows affected

SELECT COUNT(*) AS remaining FROM mysql.user WHERE user = 'old_app';
SELECT COUNT(*) AS db_grants_left FROM mysql.db WHERE user = 'old_app';

Result: 1 row

Result: 1 row

The account and its privilege rows are both gone.

3. The Host Part Matters

temp_audit exists for two hosts, which are two separate accounts. Without a host, MySQL assumes '%', which doesn't exist here:

DROP USER 'temp_audit';

ERROR 1396 (HY000): Operation DROP USER failed for 'temp_audit'@'%'

DROP USER 'temp_audit'@'localhost', 'temp_audit'@'10.0.0.%';
SELECT COUNT(*) AS temp_audit_accounts FROM mysql.user WHERE user = 'temp_audit';

Result: 1 row

4. IF EXISTS

DROP USER 'ghost'@'localhost';

ERROR 1396 (HY000): Operation DROP USER failed for 'ghost'@'localhost'

DROP USER IF EXISTS 'ghost'@'localhost';
SHOW WARNINGS;

Query OK, 0 rows affected, 1 warning

Result: 1 row

5. Objects Defined by the User

Views, stored routines, triggers and events have a DEFINER. With SQL SECURITY DEFINER they run with that account's rights. MySQL lets you drop the account anyway (with only a warning), and the objects it defined then stop working. Check first:

SELECT table_name, definer FROM information_schema.views WHERE table_schema = 'crm';

Result: 1 row

DROP USER 'contractor'@'localhost';
SHOW WARNINGS;
SELECT * FROM crm.lead_names;

Query OK, 0 rows affected, 1 warning

Result: 1 row

ERROR 1449 (HY000): The user specified as a definer ('contractor'@'localhost') does not exist

The drop succeeded, but the view is now orphaned: its definer no longer exists. Recreate it with a valid definer:

CREATE OR REPLACE DEFINER = 'root'@'localhost' SQL SECURITY DEFINER VIEW crm.lead_names AS SELECT name FROM crm.leads;
SELECT COUNT(*) AS rows_visible FROM crm.lead_names;

Result: 1 row

6. Open Sessions

Dropping an account doesn't disconnect sessions that are already logged in; they keep working until they disconnect. List and end them first:

SELECT id, user, host, db, command, time
FROM information_schema.processlist
WHERE user = 'old_app';

KILL 1234;    -- the id from the query above

7. Lock Instead of Drop

If you might need the account again, or want to be sure nothing breaks, lock it first and drop it later:

CREATE USER 'vendor'@'localhost' IDENTIFIED BY 'V3ndor#2026';
ALTER USER 'vendor'@'localhost' ACCOUNT LOCK;
SELECT user, account_locked FROM mysql.user WHERE user = 'vendor';

Result: 1 row

8. Checklist Before Dropping

1. Confirm the exact 'user'@'host' with SELECT user, host FROM mysql.user.
2. Check DEFINER objects (views, routines, triggers, events) and reassign them; MySQL won't stop you orphaning them.
3. Check active sessions and stop the application using it.
4. Optionally lock the account for a while.
5. Drop it, and remove the password from any configuration or secrets store.

9. Summary

• DROP USER 'u'@'h'; removes an account and its privileges.
• The host is part of the name; a bare name means 'u'@'%'.
• Use IF EXISTS in scripts; drop several accounts in one statement.
• Reassign DEFINER objects first (otherwise they fail with error 1449), end open sessions, or lock the account instead.