The most memorable one I used to use, mainly because I couldn't get past the Water Zone Act was the Sonic unlock all levels cheat.
On the main menu screen I recall it was Up, Down, Left, Right. You should hear a noise like a ring being collected. Then, hold A and press Start for a level select!
I also had a funny one on European Club Soccer where you could hit a ball extremely hard by entering the password Three Shredded Wheat. Bloody brilliant cheat as this game was rock hard!
What's some that you peeps remember?