For me I don't think feeling insecure in your abilities or knowledge is ever a good thing, and in this context I'd suggest it's something that doesn't so much diminish with increased knowledge, but with increased experience of the industry. Early on in your career it can be very easy to assume there is no point trying to push for certain positions, since you don't believe your knowledge is even close to what would be required. As you gain experience, especially if you work more as a generalist than a specialist, you start to recognise that's not necessarily the case.

For me I think the watershed moment was raising an MS support call years ago about a SQL replication issue, assuming I was missing something really simple, after all, what did I know about SQL! Finally got escalated to a 3rd line support person, spent hours on the phone to him, and when we finally found the resolve being told that he'd never seen that before and he'd learnt something new.

These days I feel secure in my abilities, with a better understanding of my strengths, coupled with the knowledge that there is so much more I can learn. That gives me the desire to keep trying to improve myself. Perhaps if I was in a position where I only worked in SQL then I might feel different, but then in that situation I'd hopefully know a lot more about SQL specifically than I do now!

I think umailedit's comment sums up career development in computer perfectly though!