There are accounts where people's personalities have apparently changed (for the worse), although as initial usage is usually concomitant with adolescence and all those hormones scything through the system I'm not sure how justifiable that might be.
And then there's the argument that "a little bit of weed leads onto a tab or two of LCD (edit: <= You know I meant "LSD", right?), leading onto..." and usually ending up in heroine and/or cocaine. Although that might be because people willing start off on some form of self-medication are going to be more likely to continue onto "the harder stuff".
As someone who hasn't smoked the stuff (but quite probably inhaled, in proximity, and I must say I really do dislike (legal) tobacco smoke), I can only comment from the outside, however. And that's probably part of the problem. Very few top-end legislators have both experienced it and can publicly say that they experienced it, so are probably against legalisation either through ignorance or as a form of denial (because if they appear to support it, it makes them look as least as guilty as they feel).
On the other hand, there are various experts[1] who have said that certain substances are being over-persecuted. e.g. that if you were to persist in banning certain substances you should also rule out tobacco and/or alcohol use as they're far more dangerous.
As to the OP, I would agree that it would depend upon the inherent SC environment involved. However, if the activity is not directly tied to the person involved, or of risk to their assumed honesty[2], then any fall-out from it can only really be treated as personal bias on behalf of those rescinding clearance and (again, this will depend on the organisation involved) Unfair Dismissal procedures of some kind might well be possible. Or at least a useful counter-argument that staves off the dismissal in the first place. Perhaps with some sort of mutual agreement whereby "help" is given, but down that way lies a slippery slope of judgmental conservatism that I wouldn't be happy with and I mention it only because it's what I expect some institutions might propose...
[1] Although remember the golden rule: For every expert, there's an opposite and equal expert...
[2] And this risk could be 3rd-party coming up to SCed person and saying "I'll tell your establishment/boss what Little Jimmy does every weekend if you don't do something for me", so it could end up being a circular argument if the establishment/boss already has certain views... This could also encompass all kinds of other 'vices'. Anything from gambling, homosexuality, etc through to tendency to post to 'faceSpace'-like places.