Use alter login to add, drop or change attributes of a login and their corresponding values. alter login allows you to:
Add or drop auto activated roles
Change a password
Change the login profile association
Change or add a full name
Specify the password expiration and the minimum password length
Specify the maximum failed attempts
Specify an authentication mechanism
Specify the default language and default database
Invoke a login script
Exempt inactive login accounts
A system administrator can use alter login to set password length and expiration, to limit failed login attempts, drop attributes, and to specify that a login script be run automatically when a user logs in.
After you execute alter login to change the default database, the user is connected to the new default database the next time he or she logs in. However, alter login does not automatically give the user access to the database. Unless the database owner has been assigned access with sp_adduser, sp_addalias, or with a guest user mechanism, the user is connected to master even after his or her default database has been changed.
This example changes the default database for the login account anna to pubs2:
alter login anna modify default database pubs2
This example changes the default language for claire to French:
alter login claire modify default language french
