The phrase is sometimes used in a pejorative way when referring to people from Northern Ireland, though literally it carries with it mildly mocking, or (when used by Northerners to describe themselves), perhaps ironic connotations.
The phrase can be used negatively when referring to people from Northern Ireland, but it's often used ironically by those from the region themselves.