A confident character and batsman, his combative nature earned him a one-match ban after an outburst at officials during an Intercontinental Cup game with Scotland. He also managed to incense Brian Lara when Ireland defeated the West Indies in 2004, in which he made 58. He was understudy to Geraint Jones at Kent for the 2006 season but Jones losing his England place had a knock-on effect and saw Niall released by the county. A revelation for Ireland in the 2007 World Cup, though, with two fifties and impressing with a remarkable composure for someone so inexperienced at international level. His calm, steady 72 helped Ireland pull of the biggest shock in World Cup history when they beat Pakistan, and a 63 against England underlined his ability, not to mention pluck. He joined the growing number of Ireland players in England for the 2007 season, signing for Northamptonshire.

He enjoyed considerable success at the county, being named as their Player of the Year at the end of the 2008 season and securing a three-year contract. After Nicky Boje relinquished the captaincy and Andrew Hall took up the position, O'Brien was named as his deputy. He was stripped of the position and banned from the team, however, for a breach of team disciplinary rules in June 2010. Although he was subsequently cleared at a disciplinary hearing and restored to the squad he no longer held the position of vice-captain and a fractured finger kept him out of action for most of the season.
Cricinfo staff August 2010