>It was always explained to me as a mix between, 'are you going to fuck things up by being in an altered state' and 'is someone going to blackmail you to make you into a double agent?'

You are missing the foremost consideration - how critical/specialised/irreplacable is this person in their role and can we just ignore the positive test instead.

If you are good enough at what you do and management like you positive tests dont seem to matter if you make the right noises about it being a one off, retesting clean etc.