SQL Server STIntersects Query Returns false positives

You have a ring orientation problem with your "IDoNotWork" geography instance. Specifically, the order in which you specify the points (clockwise vs counterclockwise) matters. For the IDoNotWork instance, you defined an area that is essentially Earth-sized (minus a tiny hole in London). How did I determine this? I selected the data and looked at the spatial results tab in SQL Server Management Studio. For this particular geography instance, the bounding box was (-90, 90, -180, 180). Whenever I see that, it's a good indication to me that the order of the points in the polygon was reversed from what was intended. There's a "right-hand" rule to defining the points. That is, if you were to drive around the area you want in a car always keeping the polygon itself on the right side of your car, you'll be traversing the points in the correct order.

I'm guessing that you're using SQL 2012 since this would have been an error in SQL 2008 (you couldn't have a geography instance that spanned hemispheres there). Since that's the case, there's a ReorientObject() method you can call that will re-order the points for you. Perhaps when you load you can use a heuristic (i.e. "the area of this polygon is way bigger than it should be") and if it fails the heuristic call ReorientObject on it.

TL;DR:: If you reverse the order in which you define the points (see below), you should get more of what you're looking for.

INSERT INTO Polygons (ZoneName,Coordinates) VALUES ('IDoNotWork2',geography::STGeomFromText('POLYGON ((
    51.48822 -0.13046 
    , 51.50361 -0.14626
    , 51.52071 -0.15106
    , 51.52583 -0.1181
    , 51.52242 -0.09064
    , 51.51045 -0.07828
    , 51.49763 -0.09613
    , 51.48822 -0.13046)) '
, 4326))