Summary

Admins sometimes need to find personal repositories of users who are no longer with their company or who have been removed from Bitbucket Server.

Solution

This workaround will provide you as the Bitbucket Server admin access to the repositories of removed users and give you the ability to either delete them or move them to a project of your choice.

Get a list of private repositories:

There are two SQL commands here that were created for MySQL. The basic concept is the same if you are on PostgreSQL, MS SQL Server, or Oracle but the actual SQL may need to be tweaked depending on your database type and the tool you are using to execute the SQL.

Prior to Bitbucket Server 7.0:

SELECT DETAILS, USER
FROM bitbucket.AO_BD73C3_REPOSITORY_AUDIT
Where USER IN 
 (
    SELECT USER_ID 
    FROM bitbucket.sta_normal_user 
    where deleted_timestamp 
    like '%'
    )
AND DETAILS like '%project%~%';

 Syntax for a PostgreSQL database

SELECT "DETAILS", "USER"
FROM "AO_BD73C3_REPOSITORY_AUDIT"
Where "USER" IN (
  SELECT user_id
  FROM sta_normal_user
  where deleted_timestamp is not null)
AND "DETAILS" like '%project%~%';

Alternative:

SELECT DETAILS, USER
FROM bitbucket.AO_BD73C3_REPOSITORY_AUDIT
Where USER IN 
 (
    SELECT USER_ID 
    FROM bitbucket.sta_normal_user 
    )
AND DETAILS like '%project%~%';

In both of the SQL statements, the result will give you the username (prefixed with the ~) and the name of the repository.

From Bitbucket Server 7.0 onwards:

 Syntax for a PostgreSQL database

SELECT snu.name AS username, pr.name, pr.project_key, r.slug AS reposlug
FROM sta_personal_project spr, repository r, sta_normal_user snu, project pr
WHERE spr.project_id = r.project_id
  AND spr.owner_id = snu.user_id
  AND pr.id = spr.project_id
  AND pr.project_type = 1
  AND snu.deleted_timestamp IS NOT NULL;

With this information you can do the following:

Prior to Stash/Bitbucket Server 3.9:

1. Login to Bitbucket as an admin
2. If you want to create a graveyard project create it now
3. Modify the address line to something like http://<BITBUCKET_SERVER>:7990/users/<USER_NAME_FROM_SQL>. User name is all lower case and do not include the "~". 
   Example: http://localhost:7990/users/bob
4. You will be given a list of all that users repositories (even if the user has been deleted)
5. Click on the repository and then click Properties (gear icon under the project options)
6. You now have the option to delete or move the repository to a public or private project.

From Stash/Bitbucket Server 3.9 onwards:

1. Login to Bitbucket as an admin
2. If you want to create a graveyard project create it now
3. Access each repository found with the query directly, modifying the address line to something like http://<BITBUCKET_SERVER>:7990/projects/~<USER_NAME_FROM_SQL>/repos/<REPOSITORY_NAME>/browse
   Example: http://localhost:7990/projects/~BOB/repos/repository1/browse
4. You'll access the repository settings page, which will give you the options to either move or delete the repository.

In addition, there's a feature that allows System Administrators to easily view orphaned repositories, left by deleted users. In Bitbucket Server 7.13 we have released Advanced repository management. This feature helps to get a holistic view on all repositories in Bitbucket. It can help you find all orphaned repositories in just a few clicks. Select Personal (deleted users) option in Repositories type filter :