Any user can use sp_modifylogin to change his or her full name, default authentication method, default database, default language, and default role. Use sp_modifylogin to set password length and expiration, to limit failed login attempts, and to specify that a login script be run automatically when a user logs in. A system administrator can change these settings for any user. The syntax is:
sp_modifylogin login_name, option, value
where:
login_name – is the name of the user whose account you are modifying.
option – specifies the option that you are changing. See sp_modifylogin in the Reference Manual: Procedures for a list of available options.
value – is the new value for the specified option.
After you execute sp_modifylogin to change the default database, the user is connected to the new default database the next time he or she logs in. However, sp_modifylogin does not automatically give the user access to the database. Unless the database owner has set up 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 “anna” to pubs2:
sp_modifylogin anna, defdb, pubs2
This example changes the default language for “claire” to French:
sp_modifylogin claire, deflanguage, french
This example changes the full name for “mtwain” to “Samuel Clemens.”
sp_modifylogin mtwain, fullname, "Samuel Clemens"
