I have a database user -- let's say "x" -- and I want to know how many Windows (desktop) users are using this database user (who2 or who is only give the current result, I want to know the entire history)?
Actually this question arise when you join any new organization and you don't know how many Windows or desktop users are using a specific database user. Is there any way to know this? How do you track all Windows users who are using this (x) user credential?
You could use a logon trigger perhaps, going forward (see here and here).
For historical information, by default, SQL Server doesn't even track successful logins (though this can be turned on - however it means you will need to parse the SQL Server log for some little bit of information). So while you might get some information (such as which workstations were using which logins) from the default trace, it would rely on them performing actions that get logged there (and would only go back as far as your default trace data goes anyway).
Also see this answer.
From a historical perspective, if login auditing was enabled on the server then you could see the logins in the SQL Log and use the IP to trace back to user's workstation or server. This is useless, however, if users are remoting into the SQL host and making a local connection to it (though they really shouldn't be...)
If that's not available to you, then either enable login auditing or setup an Extended Events trace to track user's workstations/IP as they login to the system.
If this is a "development box" (read: non-production) and you're concerned about too many users sharing a credential, a (potentially) drastic option would be to lock the account and see who comes to complain. This would allow you to easily assign new, individual credentials you can control to the users and is more likely to catch someone who logs in infrequently since you can keep the login disabled for an extended period of time but may not want to dig through Extended Event logs for days/weeks/months.
Finally, you could always send out an email blast and see who responds. It probably won't give you the full picture of who has the credentials, but could provide a good start and open a dialog with those who use them most frequently if you plan on making changes in the future.
