How to find personal repositories of deleted users
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
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:
1
2
3
4
5
6
7
8
9
10
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
Syntax for Postgres...
In case you are using Postgres, make sure to quote the columns and the table names as in the example below, to force the usage of the uppercase for these values.
1
2
3
4
5
6
7
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:
1
2
3
4
5
6
7
8
SELECT DETAILS, USER
FROM bitbucket.AO_BD73C3_REPOSITORY_AUDIT
Where USER IN
(
SELECT USER_ID
FROM bitbucket.sta_normal_user
)
AND DETAILS like '%project%~%';These SQL queries assume that the name of your database is "bitbucket". You will need to update the two FROM statements and replace bitbucket with the actual name of your database, and most DB types are case-sensitive.
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
1
2
3
4
5
6
7
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:
Login to Bitbucket as an admin
If you want to create a graveyard project create it now
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/bobYou will be given a list of all that users repositories (even if the user has been deleted)
Click on the repository and then click Properties (gear icon under the project options)
You now have the option to delete or move the repository to a public or private project.
From Stash/Bitbucket Server 3.9 onwards:
Login to Bitbucket as an admin
If you want to create a graveyard project create it now
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>/browseExample:
http://localhost:7990/projects/~BOB/repos/repository1/browseYou'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 :
Was this helpful?