Well, the Vengeance was not "automated" as such. It probably had a combat crew in the thousands - it was just built with enough automation that Marcus could use his mercenaries for a single noncombat sortie (that is, a sortie where there's zero chance of anybody firing back).
Crew is what you want in combat, even if it means putting lives at risk, because crew is flexible while automation even in Trek times is not. Crew can repair automation, and in extreme distress replace parts of it. Cases of automation repairing automation, or automation repairing crew, remain quite experimental in the TNG era.
As for raiding, a crewed ship is full of potential hostages. An automated ship can unleash deadly forces within for purging the raiders from her innards, without having to mind any innocent bystanders. But raiding is really an insignificant threat in Trek combat: it requires the lowering of shields, which exposes the ship to far greater risks already.
Fighting automation of the M-5 style is probably what all starships in the TNG era have as a default. The TNG ships just have a better-working on/off switch, with Worf pushing On when Picard says "Attack pattern Tango Foxtrot Lambada!" and Off when he says "Damage report" or "Suggestions" or "Hail them" or otherwise prevaricates. Full autonomy in fighting is what Starfleet would rather do without, for various reasons. But things with remote control or kill switches are okay.
Timo Saloniemi