I'm actually surprised by this. I work with a ton of people with foreign born wives (many not even green carded yet) -- soldiers tend to marry where they are first stationed.
Also, I work with plenty of people with TS/SCIs who have used drugs in the past.
The key is just being honest with the investigators.
You are right that people with too much debt, gambling problems, current drug use, etc. don't get one. But that makes sense. I've worked with some pretty sketchy characters though and they didn't have a problem getting or keeping there clearances.
I don't think I've ever personally known somebody who had theirs denied, I've known one person who had their taken from them for doing some questionable things.