Borg should be terrifying. A Borg cube is something that a starship can't take on alone. Yet, we routinely take on Borg during single-player missions. On ground missions, Borg are dispatched like every other race.
Maybe, in space missions, we should only come across and destroy Borg ships up to sphere level. A new scenario could be developed, where we take on a huge Borg cube just like the Crystal Entity scenario (it would require several ships working in absolute coordination to destroy it). It would look like the opening fleet combat scene on Star Trek: First Contact.
On the ground, I propose this for the Borg: the first wave should be extremely easy to destroy (one or two shots). The second wave would be harder, the third about as hard as the usual generic bad guys. The fourth would be hard (about as hard as the difficult setting). The fifth would have fully adapted: it would be invincible. The trick would be that the Borg would be slow, and would not fire ranged weapons, but their melee attack would be devastating. They'd look like mechanical zombies. Mission objectives would not be to "kill all Borg", but to interact with consoles, steal tech, reach a point where we can blow everything up, ect.
I really think that a lot work must be done to the Borg. As they are now, they are NOT the Borg we love to hate.