Of course he intended it to be scary.
No, he didn't. He says as much on the commentary he did for the special edition.
From about 7 minutes onwards. Winston and Cameron.
There's monsters stalking and killing people and jumping on their faces and bursting out their chests - but no, it's not meant to be scary. Just because Cameron didn't try to make it as scary as Alien - doesn't mean he didn't try to make it scary.
Uh oh SM. This guy just flopped his willy off the side of the bridge and told you the water was deep.
The bridge I'm standing on the is on Jimbob Cameron's ship over the Marianas trench and I'm telling you it's deep.