TFS4JIRA Self-Hosted | User mapping in database

This article provides information how user mapping is stored in the TFS4JIRA database.

TFS4JIRA SELF-HOSTED can use following database servers:

SQL Compact Edition (default database)
Firebird
MS SQL Express/Full

JIRA

To get Jira users we use /rest/api/latest/user/assignable/multiProjectSearch?projectKeys=mappedProjectKey and get only those with accountType equal to atlassian.

Examples:

Jira Cloud: https://damkil.atlassian.net/rest/api/latest/user/assignable/multiProjectSearch?projectKeys=STA40&query=damian

query string

A query string that is matched against user attributes, such as displayName and emailAddress, to find relevant users. The string can match the prefix of the attribute's value. For example, query=john matches a user with a displayName of John Smith and a user with an emailAddress of johnson@example.com. Required, unless accountId is specified.

Jira 9.4: http://52.2.97.38:8080/rest/api/latest/user/assignable/multiProjectSearch?projectKeys=TES&user=damian

from Jira 8.20.7 parameter: username | string | the username

Jira version	In DB	endpoint value	comment

Jira version	In DB	endpoint value	comment
Cloud	[JiraAccountId]	"accountId"
Cloud	[JiraUsername]	NULL
Both	[JiraDisplayName]	"displayName" <"emailAddress">	this value only setup what is shown in the UI in TFS4JIRA
On-Premise	[JiraAccountId]	NULL
On-Premise	[JiraUsername]	"name"

Azure DevOps Services (ADO)

ENDPOINT: https://vssps.dev.azure.com/damiankilkowski/_apis/graph/users?api-version=7.0-preview.1

RESPONSE:

{
      "subjectKind": "user",
      "metaType": "member",
      "domain": "45aa3d2d-7442-473d-b4d3-3c670da9dd96",
      "principalName": "ftotten@xyz.us",
      "mailAddress": "ftotten@xyz.us",
      "origin": "aad",
      "originId": "4be8f294-000d-4431-8506-57420b88e204",
      "displayName": "Francis Totten",
      "_links": {
        "self": {
          "href": "https://vssps.dev.azure.com/damiankilkowski/_apis/Graph/Users/aad.NjU5MDNmOTItNTNkYy03MWIzLWJiMGUtZTY5Y2ZhMWNiNzE5"
        },
        "memberships": {
          "href": "https://vssps.dev.azure.com/damiankilkowski/_apis/Graph/Memberships/aad.NjU5MDNmOTItNTNkYy03MWIzLWJiMGUtZTY5Y2ZhMWNiNzE5"
        },
        "membershipState": {
          "href": "https://vssps.dev.azure.com/damiankilkowski/_apis/Graph/MembershipStates/aad.NjU5MDNmOTItNTNkYy03MWIzLWJiMGUtZTY5Y2ZhMWNiNzE5"
        },
        "storageKey": {
          "href": "https://vssps.dev.azure.com/damiankilkowski/_apis/Graph/StorageKeys/aad.NjU5MDNmOTItNTNkYy03MWIzLWJiMGUtZTY5Y2ZhMWNiNzE5"
        },
        "avatar": {
          "href": "https://dev.azure.com/damiankilkowski/_apis/GraphProfile/MemberAvatars/aad.NjU5MDNmOTItNTNkYy03MWIzLWJiMGUtZTY5Y2ZhMWNiNzE5"
        }
      },
      "url": "https://vssps.dev.azure.com/damiankilkowski/_apis/Graph/Users/aad.NjU5MDNmOTItNTNkYy03MWIzLWJiMGUtZTY5Y2ZhMWNiNzE5",
      "descriptor": "aad.NjU5MDNmOTItNTNkYy03MWIzLWJiMGUtZTY5Y2ZhMWNiNzE5"
    }

TABLE: [dbo].[UserMappings]

COLUMN: [TfsUserDisplayName]

VALUE: "displayName" + <"principalName">

"principalName" - in case of duplicates in "displayName"

example: Francis Totten <ftotten@xyz.us>

TFS / ADO On-Premise

TABLE: [dbo].[UserMappings]

COLUMN: [TfsUserDisplayName]

VALUE: "displayName" + <DOMAIN\ACCOUNT>

DOMAIN\ACCOUNT - in case of duplicates in "displayName"

example: Francis Totten <PC-1223\FTotten>