Usually with Prime Ministers it takes a while for history to settle on a verdict.
When PMs leave usually its at a point of weakness and the immediate commentary is negative and also they are associated with one thing: financial crisis (Brown), Iraq (Blair), Black Wednesday and the ERM (Major), Poll Tax (Thatcher), winter of discontent (Callaghan), IMF (Wilson), Miners strike (Heath), Suez (Eden).
Then they get to release their own memoirs which puts over their side and gives some balance. Then after a while later you get a revisionism of them being underappreciated. The view of John Major has become more positive in recent years because people have started to think how remarkable it was for a guy from a council estate in Brixton who didn't go to university to end up as PM, make major steps in the Northern Ireland peace process which had been deadlocked (we had much more frequent terrorism in the UK back then than we do now believe it or not), and also his record in Europe looks better now as falling out of the ERM helped the economy in the long run, and people realised he actually got the UK a good deal at Maastricht with our opt outs which meant we had a unique status in the EU. And also people appreciated his general integrity and good manners, he was the last PM before the era of spin and never went in for the gutter politics you see today.
I expect the immediate verdict on Cameron will be negative. He basically took two almighty gambles and failed with the Scottish and EU referendums. He leaves office with the UK on the verge of two major events, leaving the EU and breaking apart with Scotland at some point soon likely to leave. So his legacy will be changing the UK in seismic ways. Now over time there's a chance history will say actually that the UK did ok outside the EU and without Scotland but what is certain is that wasn't what Cameron intended to happen given his often repeated (and generally sincere) proclamations about his love for his country.
He had one very good achievement which was an against the odds election win in 2015, and also not to be underestimated is that he held together a Coalition government, which people didn't think would be possible. Other than that he was largely a pragmatist, steward of the ship kind of PM, he didn't have a big ideology or a big vision where you can point to him leading major reforms. On this point he's kind of a 6.5 out of 10 PM.
I think he is generally a socially liberal person and he got the Tories to change on gay marriage and he often said the right things about the UK being a tolerant and diverse society. However when he was in Opposition, he talked a lot about "Broken Britain" and unfortunately Britain is more broken now than it was when he came in. Britain is more divided, which I don't think he intended, but some of it is because he has played with tensions for political purposes. In Opposition he used immigration as a stick to beat Labour with, creating a narrative of the Conservative years being sensible controlled migration and Labour encouraging mass migration which he would get down. But having staked his position on "getting it down to the tens of thousands" he ended up seeing migration higher than it was under Labour, which he then tried to claim as evidence of his economic success... In the end bringing migration up as a political weapon ended up seeing UKIP claim that ground and be a threat initially to the Tories, now more to Labour - so he's had political gain there but at the expense of increasing racial tension which I expect he didn't want to stir. He also however showed he was willing to play on prejudices with his campaign against Sadiq Khan which backfired as Londoners rejected the tone and are now rallying around Sadiq and there's a dangerous kind of "London exceptionalism" developing similar to Scottish nationalism which is not ideal.
I also think he ended up failing by some of his own yardsticks. Early on he talked a lot about needing a strong Tory government as under Labour we 'may' have lost our AAA credit rating. Well we lost it in 2012, regained it and have now lost again. But worse than this, I remember last year at the election he said the choice was vote Conservative and get a strong government which will bring stability and keep the union together, or vote Labour and get chaos, market uncertainty, years of instability and backroom deals which would paralyse normal government, the Scottish Nationalists holding the government to ransom. He has basically ended up delivering exactly that scenario himself.