I think the key is moderation. I enjoy a cold beer, a joint, or a glass of wine in the evening - not all together mind you. These things are useful, help you relax, and in some cases think creatively.
Used in excess, any of them can destroy your life. So for me, a critical factor of whether a substance is "good" or "bad" is the ability to recognize it's effects, and control it. People differ in their ability to do this, and outside forces (problems in your life) can complicate the picture. Substances also vary in their ability to be controlled.
The war on drugs is a complete farce and should be ended. The main effect of criminalizing drugs has been to insanely drive the price up and put most of that money into the pockets of some pretty nasty people. Look at what's happening in Mexico.
People have been using various substances to get high for all of human history, and they will continue to do so regardless of what their various governments desire. The best solution IMO is to honestly educate people about the effects of various substances and then let them decide for themselves. Most people will make responsible choices, a few will crash and burn, but the overall negative impact on individuals and the society as a whole would likely be less than what we have with the current approach.
This is an incredibly loaded topic, because human beings are frequently very judgmental about the things that provide comfort, pleasure, or solace to others (e.g, drugs, sex, and religion).