2 clear aspects to this - technical and moral.
Technical - he was a pilot, responsible for the lives of all who flew with him. Furthermore, he was doing so in a very demanding and challenging environment. Anything which he knowingly did to inhibit his performance (notwithstanding his boasts on how certain things were enhanced) is just plain wrong and unacceptable.
Moral - he was an officer in HM Armed Forces. He has a duty and obligation to set and maintain the highest of standards and set the right example to his peers and juniors. Doing what he did shows an absolute lack of moral courage and furthermore, to compound the issue, it seems he encouraged others to engage in what he clearly knew to be illegal activity.
Finally, I find it astounding some on here feel it appropriate to advocate a 'one rule for officers, one rule for sailors/soldiers/airman' approach. HM Forces have very few absolute zero tolerance boundaries - drug taking is one of them and therefore should be treated accordingly in every circumstance, regardless of rank or rate.