In the current software this has to be done by an administrator, and is usually done only in very exceptional cases for some members who have been here for a long time and (usually) who have been part of the moderation team. There is at least one exception to the later "requirement", but in that case it was... fitting. That person does not stalk these parts any longer though.
If I recall correctly, the new software will support a limited ability for users to select a title for themselves based on some of the achievements they've earned. They won't be entirely custom though; each achievement has a predefined title associated with it. Completely custom titles will be handled the same way they are now.