Well, don't be too harsh either. If all he did was try it ONCE and not since and does not currently use any illicit substances or participate in any other criminal undertakings, then you don't have to stop being his friend. What is meant here is that if you were keeping the company of someone who regularly does those things and/or is a troublemaker of some sort, then THAT would definitely reflect upon you.
I have a friend myself who told me he tried LSD a long, long time ago. He's not a police officer of course. He has a wife, two kids, a very good job, and is a very responsible citizen. I didn't say, "Oh crap I didn't know you ever tried that stuff. That's it, friendship is over, you never should have done that!!!!" I think it's really just common sense on what we're talking about here in terms of the company you keep.
However, having EVER tried a hallucinogenic substance is an immediate disqualifier at most agencies I'm familiar with anyway. There are physiological reasons for that. In this way, yes, it will have permanent consequences for your friend as he will need to find another career.
"Now I can just tell people that I'm old, cranky and to leave me the hell alone!" --retdetsgt
"I'm a certified Bovine Scatology Detector" --Creeker