Years ago, I had a friend who was a great person, but had a drug problem. She smoked marijuana, and took pills, and I don't even know what all else. She knew I didn't approve and didn't do it around me, or in our home.
When my kids were teens, she came to the house to visit after having moved away to Florida. She was really messed up and couldn't talk coherently, her movements were spastic, her thoughts disjointed. All she could talk about was how she was now clean, and hadn't touched any drugs in months. It scared my kids to death. Dennis and I talked about it, and decided perhaps it was good for the kids to have seen her like this, as she did not make a good impression on them at all. They came to me after she left and said, "if that's what drugs do to you, I want no part of them." And they haven't been drug users.
Now alcohol, that's a different story. My kids are all grown now, and they remember growing up with their dad as an alcoholic. They do drink, but in moderation. I think seeing their dad at his worst moments and my friend at hers has made a very vivid impression on them.