I've certainly had nerve racking moments where my login has been flagged as unusual and I wasn't sure if it would let me in (and I'm completely locked out of my childhood account though it's not been used in over 10 years)
It's a good feature for those with the password "password" but if you've used a strong single use password it just gets in the way