There are some good ways around this problem. First, use Windows accounts instead of SQL accounts. Windows accounts don’t pass passwords between boxes; they only pass their token, so there’s nothing to steal. My devs get tired of me turning down their requests for SQL accounts. I hand out SQL accts when there’s no other option, and I severely limit the access.
Also, don’t create generic SQL accounts. Should you have a password that gets stolen in the manner I’m going to show you, you have someone to go to.
You can interrogate this person and try to ascertain their activities at the time of the activity. If you’re able to clear the end-user, it’s easier to determine whether you’ve been compromised. But if you have a generic account, it’s much harder to diagnose the situation as a security compromise. You’re far less likely to come to the right conclusion -- at least for a very long time.
You can use this to get SQL passwords that have been lost and can’t be retrieved or changed any other way. I currently have such a situation. I inherited a SQL account that’s used all over the LAN and by so many processes there’s just no way I could change it without breaking hundreds of settings.
If you have a situation like this, you’re screwed if someone comes in and changes the password in the DB by mistake.
Another good example of this is with linked servers. You can’t get linked server passwords back out once you create the linked server, so if you lose the password you’re screwed. Again, I'm in this exact situation myself. I have several linked servers that hit boxes in other groups or divisions and nobody on the other side knows the password. We can’t change it because they have no idea how many groups are using it.
Whatever your reason, you need to get a password out of SQL Server’s memory. Go to my video on MidnightDBA.com to see me walk you through the process painlessly. In fact, it’s so easy it’s actually scary.
I hope a lot of you found this useful and I hope it finally puts to rest whether this is something Microsoft should change -- because they should.