Veteran stand-off Brough looked to have cost his side victory when first messing up to concede a try and then missing a simple penalty as Rovers bravely fought back from 22-4 down after 48 minutes to level 22-22 on the hour.
A draw would have been enough to keep Rovers up but they must now host Salford Red Devils or London Broncos next Saturday for the last remaining Super League slot.
They had a Thomas Minns try disallowed in the 79th minute but it was the correct decision, Terry Campese having shoved Jake Connor off the ball as the captain chased his own chip over the top.
It was still tough on Rovers but the damage was done in the first half.
Ryan Brierley certainly proved his match-winning quality when it mattered most.
The srum-half, bought from Championship Leigh Centurions in March, has not always been in favor since Australian Rick Stone took charge in July.
But the 24-year-old has been preferred to Jamie Ellis for the last two games and repaid the coach’s faith in him in this crunch final Qualifiers contest dubbed Survival Saturday.
Both sides knew whoever won would be safe and it was Brierley-inspired Huddersfield who initially took control in east Hull, easing into that 22-4 interval advantage.
He raced 60m for the first try inside just five minutes having intercepted Campese’s pass and - after Maurice Blair replied for the hosts - then dummied and arced clear on a lovely break from insisde his own half to set up England centre Leroy Cudjoe.
Rovers were in disarray when Ukuma Ta’ai plundered up the middle to find Brierley on his shoulder for his second try on 27 minutes, Danny Brough adding his third conversion.
There was yet more chaos when Mantellato returned a kick and, for some reason, tried passing to Ben Cockayne who fumbled and was then sin-binned for holding Cudjoe down in the tackle.
With Rovers down to 12, Brough’s cut-out pass saw Aaron Murphy sneak in at the corner and, though Will Jubb fumbled going over the line in reply, the hosts were staring that 22-4 interval deficit.
However, they clearly returned in a better frame of mind, Mauric Blair driving them forward well supported by Adam Walker.
Campese’s grubber forced a drop-out and, from there, they began their fightback with, for once, some slick handling seeing Ken Sio in at the corner on 49 minutes.
Mantellato converted and Rovers could not believe their luck when, four miniutes later, Brough made a complete mess of trying to clear a Matty Marsah grubber behind his own line.
Mitch Allgood was on hand to pick up the pieces and Mantellato cut the deficit further with his conversion.
Brough missed his simple penalty attempt and that looked costly when Iain Thornley scored.
England winger Jermaine McGillvary bizarrely let Blair’s long kick bounce and Mantellato’s challenge saw the ball come clear for the Rovers centre, Mantellato levelling with his conversion.
However, Jamie Peacock - the Hull KR football manager out of retirement - was caught offside when handling James Donaldson’s dropped pass and, from that penalty, Huddersfield found position for Brough to score.
Campese struck a post from 40m out as he tried with his own effort and then came that late attempt from Minns but it was Giants who held on.
Hull KR: Cockayne; Sio, Minns, Thornley, Mantellato; Campese, Marsh; Tilse, Lunt, Allgood, Blair, Clarkson, Donaldson. Substitutes: Greenwood, Walker, Peacock, Jubb.
Huddersfield Giants: Grix; McGillvary, Cudjoe, Connor, Murphy; Brough, Brierley; Huby, Hinchliffe, Ta’ai, Symonds, Wardle, Lawrence. Susbstitutes: Wood, Mason, Roberts, Ikahihifo.
Referee: Robert Hicks (Oldham)