Simon Rose said the public had 'unrealistic expectations' of support and services available from local authorities