Depends how much you want to spend: Caterhams are around £200/day, but if you have a twisty road ANY hire car will be decent. There are no really crappy hire cars in the UK like there are in America with your crappy Chevy Cruzes and the like. I rented an 3cylindered Toyota Aygo to go to Goodwood and it was the most fun I've ever had in a car.
However, there are Fiat 500 Abarths aplenty and even the standard 500 should suffice for driving fun on most roads.
Edit: Google is your friend. Look up "Fiat 500 Abarth hire Scotland" or wherever you are going + car of your choice and see what comes up