To change a user's (login's) password via Transact-SQL, use this:

ALTER LOGIN User WITH 
     PASSWORD = 'new-password' 
     OLD_PASSWORD = 'old-password';
GO