I think it depends on the job, and if the substance problem is actually affecting their behavior and work performance. I think the focus should solely be on those 2 things, and not on whether the person uses drugs or not. Most people with serious alcohol and drug problems will have poor work performance, and that should be the thing they are judged for in that scenario.

There should probably be limits to professions like doctors, pilots, and drivers, in my opinion. The thing is that some drugs(in the right amounts) make people perform better at these jobs. Our pilots in the US military still carry meth pills with them for long missions. If I was on a long flight, I would definitely want the pilot to remain awake and aware the whole time. If a stimulant helps them with that, then I don't mind.

As for law enforcement, I think they should be required to get high and relax, at least on their off time. Most of our killology trained cops seem to be in constant fight or flight mode with the public. They seriously need something to calm their nerves and ground them in between shifts, and right now, their go to is alcohol, which is worse than other drugs they could partake in.

If other lives depend on said job, then yea they should probably be tested. The vast majority of jobs are just meaningless drivel, though, and whether the employee does drugs or not shouldn't matter.