A curious user decided to ask Steam directly if after his death he could transfer his account and games to someone else through a will. Valve's answer was clear: the account is unique to the user and cannot be transferred, even after death.
It seems that the account is yours and yours alone and cannot be transferred to other users. Of course, nothing prevents you from leaving the login and password to someone else, unless Steam is planning to deactivate the accounts after a certain number of years.
User: Hello Steam support, this is a quick question to quell an issue that has been plaguing me. I don't plan on dying any time soon, but when I do, can ownership of my Steam account be transferred through a will?
Steam Support: Hi, thanks for contacting us. Unfortunately Steam accounts and games are not transferable. Steam Support cannot provide another person with access to the account or merge its contents with another account. I regret to inform you that your Steam account cannot be transferred through a will.
It seems that the account is yours and yours alone and cannot be transferred to other users. Of course, nothing prevents you from leaving the login and password to someone else, unless Steam is planning to deactivate the accounts after a certain number of years.