The first safe drug consumption room in the UK will “offer hope” to hundreds of people, community leaders have said.