Metroid's (the ENGAGE RIDLEY MOTHER ****ER password) I believe messes up the MMC1 registers somehow, causing it to jump to invalid code. The results are thus dependent on how invalid opcodes are handled.
On a real NES, it blank screens (tested with both the silver and yellow carts). (people have claimed it can brick their carts, but I don't see how that is even possible, considering it doesn't have a battery to retain the RAM contents)
On emulators, it seems to put Samus in Brinstar but without screen scrolling enabled.
The GBA release (unlockable in Zero Mission all versions, but I assume the US/EU "Classic" version is simply the ZM unlockable sold as a standalone game) soft-resets to the title screen. (JP Mini is based on FDS so it lacks the glitch)
I know when the 3DS VC version was first released (as a free download to registered "Ambassador" members who bought the console while it was still $250 MSRP), that password was the first thing someone tried and they said it hard-locked their console. I would guess/hope Nintendo would've fixed that by the time it was sold commercially (the Ambassador releases were clearly VC prototype releases, and I think they essentially said as much, though they did allow the Ambassadors to upgrade to the final versions free)