Different John Smiths may have different, given middle names. But, yes, banks and similar institutions use the Social Security number as an identifier.
In traditional American culture, the children would assume the father's surname at birth (the mother would at marriage).
This would still be the case in the majority of cases today. But some families choose to give the children both mother's and father's surnames (often hyphenated). Some children are born to single mothers or in other circumstances where the mother's name is assigned to the child.