Summary

When trying to add an SSH key for a user the following error occurs:

This SSH key is already used to access a repository or project.

In a large instance of Bitbucket Server/DC it can be difficult to find which repositories and/or projects use this access key.

This guide will help you determine which project or repository an SSH key is being used for as an access key.

Environment

Bitbucket Server/DC

Solution

Unfortunately, there isn't a great way to do this in Bitbucket Server at the moment. It's possible, but it will involve querying your database and/or making REST API calls.

Solution via dabatase:

The easiest way to accomplish this is by running a query similar to the following:

1. Identify the public key text, this will be similar to the contents of ~/.ssh/id_rsa.pub

   ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC3eahbupC1FeZQCZd7BbTioBnRFwiO39OpsdmN8Bn5MWM1Qg6pPZIr8j5OAAGZ9Qyn307Bfu1K5rFddsPoat69FLPaQAMGSysa59A62MJkYnBIDDOrue/GQpXmx3Ns2ntQdgUH9ls67uPdK7ca2Q2J2MNJUJSbPZA7FwrOiejnL6gLOT6V2hTy6Hjl8L7s3KrFJgZnKX8YcujErHHqBw8d9PIak9PfafcvUBwbkRrYHun967lbJ+HJpaEuXkSWuRkWyGpBAaOMx1tQVAbwNLbPJib/i+QlQIf9DR6GQRIVQX8NJB/KH30cyfg1YoVhmCVb5xgh8irBsJkRux83owC9 example@company.com

2. Using the public key, run the following query on Bitbucket's database to find the Project this key may be associated with:

   select project.name, project.project_key from "AO_FB71B4_SSH_PUBLIC_KEY" spk
   join sta_project_permission spp on spp.user_id = spk."USER_ID"
   join project on project.id = spp.project_id
   where spk."KEY_TEXT" = 'ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC3eahbupC1FeZQCZd7BbTioBnRFwiO39OpsdmN8Bn5MWM1Qg6pPZIr8j5OAAGZ9Qyn307Bfu1K5rFddsPoat69FLPaQAMGSysa59A62MJkYnBIDDOrue/GQpXmx3Ns2ntQdgUH9ls67uPdK7ca2Q2J2MNJUJSbPZA7FwrOiejnL6gLOT6V2hTy6Hjl8L7s3KrFJgZnKX8YcujErHHqBw8d9PIak9PfafcvUBwbkRrYHun967lbJ+HJpaEuXkSWuRkWyGpBAaOMx1tQVAbwNLbPJib/i+QlQIf9DR6GQRIVQX8NJB/KH30cyfg1YoVhmCVb5xgh8irBsJkRux83owC9 example@company.com';

3. The key may be used at the repository level, if so, the following query will review the repository that the key belongs to:

   select repository.name, repository.slug, project.name from "AO_FB71B4_SSH_PUBLIC_KEY" spk
   join sta_repo_permission srp on srp.user_id = spk."USER_ID"
   join repository on repository.id = srp.repo_id
   join project on project.id = repository.project_id
   where spk."KEY_TEXT" = 'ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC3eahbupC1FeZQCZd7BbTioBnRFwiO39OpsdmN8Bn5MWM1Qg6pPZIr8j5OAAGZ9Qyn307Bfu1K5rFddsPoat69FLPaQAMGSysa59A62MJkYnBIDDOrue/GQpXmx3Ns2ntQdgUH9ls67uPdK7ca2Q2J2MNJUJSbPZA7FwrOiejnL6gLOT6V2hTy6Hjl8L7s3KrFJgZnKX8YcujErHHqBw8d9PIak9PfafcvUBwbkRrYHun967lbJ+HJpaEuXkSWuRkWyGpBAaOMx1tQVAbwNLbPJib/i+QlQIf9DR6GQRIVQX8NJB/KH30cyfg1YoVhmCVb5xgh8irBsJkRux83owC9 example@company.com';

4. It's also possible the key is associated with a user profile. You may also run the following query to verify if the key belongs to a specific user:

   select snu.name, snu.slug from "AO_FB71B4_SSH_PUBLIC_KEY" spk
   join sta_normal_user snu on snu.user_id = spk."USER_ID"
   where spk."KEY_TEXT" = 'ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC3eahbupC1FeZQCZd7BbTioBnRFwiO39OpsdmN8Bn5MWM1Qg6pPZIr8j5OAAGZ9Qyn307Bfu1K5rFddsPoat69FLPaQAMGSysa59A62MJkYnBIDDOrue/GQpXmx3Ns2ntQdgUH9ls67uPdK7ca2Q2J2MNJUJSbPZA7FwrOiejnL6gLOT6V2hTy6Hjl8L7s3KrFJgZnKX8YcujErHHqBw8d9PIak9PfafcvUBwbkRrYHun967lbJ+HJpaEuXkSWuRkWyGpBAaOMx1tQVAbwNLbPJib/i+QlQIf9DR6GQRIVQX8NJB/KH30cyfg1YoVhmCVb5xgh8irBsJkRux83owC9 example@company.com';

Solution via rest API:

Use the first 30 or so characters of you SSH key to make sure it's unique. This will return the KEY_ID of that key.
You can then run a REST API call with the endpoint /rest/keys/1.0/ssh/<KEY_ID>/projects and /rest/keys/1.0/ssh/<KEY_ID>/repos.

Examples:

First, list all keys by opening this url:

http://<bitbucket_url>/rest/ssh/1.0/keys

This example will use 45 as the KEY_ID:

http://<bitbucket_url>/rest/keys/1.0/ssh/45/repos
http://<bitbucket_url>/rest/keys/1.0/ssh/45/projects

You can also use a curl command rather than a URL:

curl -u <username>:<password>-v -X GET -H "Content: application/json" http://<bitbucket_url>/rest/keys/1.0/ssh/<id>/repos
curl -u <username>:<password>-v -X GET -H "Content: application/json" http://<bitbucket_url>/rest/keys/1.0/ssh/<id>/projects

This will return a list of repositories or projects that use this key. You can then use REST API to delete those keys or manually delete them from the UI if there aren't many.

If you want to delete all access keys with the same ID you can use a curl command like the following:

curl -i -u <username>:<password> -X DELETE -H "Content: application/json" -H "Content-Type: application/json" http://<bitbucket_url>/rest/keys/1.0/ssh/10

Please see our documentation here for more REST API endpoints related to SSH keys.