SSMS User Mapping Slow to Load, or Not Responding

I have been experiencing an issue in SSMS versions 14, 18, and 19, where loading the User Mapping of any given Login takes an age to load, sometimes up to an hour, or just never loads at all and hits not responding.

Has anyone experienced this before and/or able to give some guidance on resolving this?

I have tried the following:

Confirmed it does not matter if it is an AD login or SQL login
Upgrading SQL Server version from 2016 to 2022
Upgrading SSMS version from 14 to 18 and to 19
Checked for any SQL blocks (there are none)
Tried on multiple different logins (they all seem to experience this)
There are 25 databases on this SQL instance, and approximately 50 Logins in total.

Unfortunately this issue is making it incredibly difficult to manage user security and mappings to databases without writing some SQL query.

Any support or guidance would be appreciated!

EDIT - I have run a SQL Trace and have identified the following query to be the offender:

USE [database_name]
SELECT
u.name AS [Name],
CAST(CASE dp.state WHEN N'G' THEN 1 WHEN 'W' THEN 1 ELSE 0 END AS bit) AS [HasDBAccess],
ISNULL(u.default_schema_name,N'') AS [DefaultSchema]
FROM
sys.database_principals AS u
LEFT OUTER JOIN sys.database_permissions AS dp ON dp.grantee_principal_id = u.principal_id 
and dp.type = @_msparam_0
WHERE
(u.type in ('U', 'S', 'G', 'C', 'K' ,'E', 'X'))
and
(ISNULL(suser_sname(u.sid),N'')=@_msparam_1)

It runs after selecting a DB to run on, and is the 4th query to run.

EDIT 2 - It is specifically this part that is causing the problem and i have no idea why:

    and
(ISNULL(suser_sname(u.sid),N'''')=@_msparam_1)

EDIT 3 - I am pretty sure it is the "suser_sname" function, having tested it against different DB's and different logins. It just hangs, and never completes, forcing me to close SQL.

Any further guidance would be appreciated, thank you.

EDIT 4 - Further context, apologies for original omitting of this info.

This issue is occurring on a separate domain, our DEV environment. It is where our Production databases are restored and as a part of that restoring, associated SQL logins are updated to work on the DEV domain where required.

I wondered if perhaps some orphaned SQL logins were causing this issue with some mismatch of the SID in sys.database_principals. Yes, there were some orphaned logins but upon fixing these up with sp_change_users_login, this issue still occurs.

Solution

TL;DR - Ensure you have a robust user/login clean up when restoring a database from one Windows domain to another!!

Full Answer: As mentioned in my question, the issue resides in our DEV environment where databases are restored from our PROD environment.

When we do this, we run a refresh script that updates users and logins to its specific Windows environment. E.g. PROD\L.Moy becomes DEV\L.Moy, when refreshing from Prod domain to Dev domain.

What this refresh script does NOT do is take into account and remove non-required users and logins. A non-required user and login still contained its PROD domain as a part of the Windows user name. E.g. in our DEV SQL Server, you could still find user PROD\foobar as a user against the DB despite not being needed.

What this meant for me was that I had a combined ~40 PROD Windows SQL Users against 25 DB's that were NOT required for our DEV environment.

So keeping the above in mind, when you click "User Mapping" against a Login, it runs a system function called "SUSER_SNAME". When I ran this function specifically against SID's for PROD users and logins in our DEV environment, it took just over a minute for each one. If you run "SUSER_SNAME" with an SID for a login that is supposed to be in DEV, it completes immediately.

So the solution here was to actually clear up all of the logins and users that were not actually needed, from all DB's within the DEV environment.

Then when selecting "User Mapping" against a login, it completes immediately, no performance issues at all.

Thank you to @Stephen Morris - Mo64 for their guidance and suggestions.