Usually when users register on a site does the email confirmation contain their username and password? When the user clicks the activation link does it make the user sign in or deep link them into a page without signing in?
A confirmation email of some sort is a must, I think.
About the auto login, I've always found having to login after activating an account a bit annoying. I'm not sure if this common practise helps reinforcing the user and pass association or it's easier because the system doesn't have to store this info. But when I sign up to a service it's usually because I need to use it immediately, so adding an extra step in that process always seemed unnecessary. There might be a technical reason behind it that I'm not aware of, though.
Usernames are sent very often, but not necessarily. If a user was asked to put in his name, then, most probably, in the email it will say "Hello [name]," etc. However, passwords should never be sent via email, for security reasons. Activation links often just link to a page where there is a confirmation message and sometimes they don't even show you any confirmation and just load the home page, but without logging in the user. In some rare cases, I've seen that the activation link also logs in the user.
This is a process that I've given some thought to lately.
As to confirmation emails, those are almost expected nowadays. The username can be reiterated as part of the greeting, as in "Hello <username>". It is a bad idea to email the password to the user, it introduces security and trust concerns as to if the password is stored and communicated over cleartext.
Regarding the automatic login issue, a reasonable compromise could be the following flow:
A large portion of users are likely to complete a sign-up in a single sitting, clicking the verification link as it is received. This method removes the annoying login step from the common case. At the same time, it doesn't make it possible for someone to intercept the link, and hijack the account.
I'd disagree slightly with what folk are saying here.
Security hat on.
E-mailing a plain text password isn't a security faux pas because of the e-mail.
It's a security faux pas because it means that you are storing the password in plain text on the server. This, in turn, means that any server-side security failure giving attackers access to the password database means everybody's password is at risk.
If email was a useful attack vector for individuals then nobody would do password resets via email (since if an attacker can read your password in an email, they could also get at your password reset URL - and pwn you that way).
So - you shouldn't do it because you shouldn't be able to do it. Not because emailing passwords are a major problem themselves.
(It is a minor risk - since email is more vulnerable to attack that some other kinds of communication. But if you trust email enough to do password resets - you are saying that anybody with access to that email can get control of the account. This is why you will see sites that have real security implications doing offline password resets, or password resets via on-line security questions, or using two factor authentication of some kind, etc.).
Ok - security hat off. UX hat on.
Some things to think about:
TL;DR - it depends ;-)