How to add a new column, with a default value, to an existing table in SQL Server  

Here is the generic script you are looking for.

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]

In your scenario you may have something like this:

ALTER TABLE [User]
ADD [LoginCount] INT NOT NULL
CONSTRAINT LoginCountConstraint DEFAULT 0

Alternatively, use SQL Server Management Studio. Simply right click on the table and select Design. Then add the column to the list with a default value.

This is the simplest way in my view.