For about a decade or so, Coffey was the top offensive defenseman in the league. Although he wasn't the only notable addition in that time frame, he did play a huge role in helping Lemieux lead the Penguins from Patrick Division also-ran to Stanley Cup Champions.
I suspect a huge portion of your impression of Coffey is colored by context--in particular, the NHL's style of play by the time Coffey made it to Detroit, vs. the style of play when he was in Edmonton, and even Pittsburgh just a few years previous. In the 80s and early 90s, the more wide-open play of the NHL suited to his strengths; but the more close-checking style of the mid-90s made him a dinosaur. Similar to how Derian Hatcher was a top defender before the lockout, but was exposed as a pylon when the game was widened back up.