Why 1806 in particular, out of curiosity?

So, there wasn't really a concept of an "honorable discharge" for a captain or lieutenant; once a man had ben commissioned as a lieutentant, master and commander, or post-captain, he was entitled to half-pay (that is, being paid half his normal annual salary) if not in active service. The history of half-pay is a bit out of scope here, but it was essentially a way to provide officers with some money to live on as the size of the navy surged and fell during war/peacetime.

If an officer wanted to leave service, he could resign his commission, which may be the closest analogy to an "honorable" discharge.

Moving to what kind of offenses would result in expulsion from the Navy, pretty much anything mentioned in the Articles of War would do. Those cover gross misconduct and dereliction of duty, as well as treason, supplying aid to an enemy, etc.

As far as prison, the Navy of this period didn't have anything like a permanent naval prison. If an officer were found guilty in a civil or criminal court, he could be stripped of rank, but the navy per se would not punish with prison.

It's worth noting that the Articles of War do mention capital punishment as a consequence. The most famous victim of that was probably Admiral John Byng. He was convicted of failure to do his utmost to capture Minorca in 1757, and was shot on his own quarterdeck. This is the origin of Voltaire's quip in Candide: Dans ce pays-ci, il est bon de tuer de temps en temps un amiral pour encourager les autres (in this country, it is good from time to time to shoot an admiral, to encourage the others).

And could an ex-officer go on to command a merchant ship or privateer?

Yes, absolutely. The Royal Navy's Transport Service was also a path to command for some lieutenants who did not otherwise have prospects of command.