Any user can use sp_modifylogin to change his or her default database, default language, or full name. sp_modifylogin only effects user roles, not system roles. A System Administrator can change these settings for any user. The syntax is:
sp_modifylogin account, column, value
where:
account – is the name of the user whose account you are modifying.
column – specifies the option that you are changing. The options are:
defdb – The “home” database to which the user is connected when he or she logs in
deflanguage – The official name of the user’s default language, as stored in master..syslanguages
fullname – The user’s full name
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"
