SQL to unlink contacts and drupal accounts

Question

1) In what table/field of the CiviCRM database is the Drupal User ID stored?

2) What is the SQL statement to delete all rows in that table except User1?

Due to the way we imported contacts and generated Drupal users, all our contact/user links are mismatched (on a development site). And I just can't get phpMyAdmin working on this server, so need to do it via mysql CLI.

Answer 1

The table you are looking for is civicrm_uf_match with the following structure

CREATE TABLE `civicrm_uf_match` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'System generated ID.',
  `domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this match entry for',
  `uf_id` int(10) unsigned NOT NULL COMMENT 'UF ID',
  `uf_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'UF Name',
  `contact_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Contact ID',
  `language` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'UI language preferred by the given user/contact',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UI_uf_name_domain_id` (`uf_name`,`domain_id`),
  UNIQUE KEY `UI_contact_domain_id` (`contact_id`,`domain_id`),
  KEY `I_civicrm_uf_match_uf_id` (`uf_id`),
  KEY `FK_civicrm_uf_match_domain_id` (`domain_id`),
  CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_civicrm_uf_match_domain_id` FOREIGN KEY (`domain_id`)  REFERENCES `civicrm_domain` (`id`)
 )

So the SQL statement to delete every link except the admin user is:

delete from civicrm_uf_match
where uf_id !=1

The batch found at Administer -> Users and Permissions -> Synchronize Users to Contacts brings them back. But better try this first on a test instance.