{"id":3175,"date":"2022-04-10T09:42:31","date_gmt":"2022-04-10T02:42:31","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3175"},"modified":"2022-04-10T10:18:04","modified_gmt":"2022-04-10T03:18:04","slug":"sql-server-alter-login","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-administration\/sql-server-alter-login\/","title":{"rendered":"SQL Server ALTER LOGIN"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the SQL Server ALTER LOGIN<\/code> statement to change the properties of a login account.<\/p>\n\n\n\n

To change the properties of a login account, you use the ALTER LOGIN<\/code> statement. Before using the ALTER LOGIN<\/code> statement, let’s create a new login first:<\/p>\n\n\n

CREATE<\/span> LOGIN bobcat\nWITH<\/span> PASSWORD<\/span> = 'Mou$eY2k.'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Disable a login #<\/a><\/h2>\n\n\n\n
The following ALTER LOGIN<\/code> statement disables a login account:<\/p>\n\n\n
ALTER<\/span> LOGIN login_name\nDISABLE<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement disables the login bobcat<\/code>:<\/p>\n\n\n
ALTER<\/span> LOGIN bobcat\nDISABLE<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Once disabled, you cannot use the bobcat to log in to the SQL Server.<\/p>\n\n\n\n
Enable a disabled login #<\/a><\/h2>\n\n\n\n
The following ALTER LOGIN<\/code> statement enables a login:<\/p>\n\n\n
ALTER<\/span> LOGIN bobcat\nENABLE<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement enables the login bobcat<\/code>:<\/p>\n\n\n
ALTER<\/span> LOGIN bobcat\nENABLE<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Once enabled, you can use bobcat<\/code> to log in to the SQL Server.<\/p>\n\n\n\n
Rename a login #<\/a><\/h2>\n\n\n\n
The following ALTER LOGIN ... WITH NAME<\/code> change the name of a login to a new one:<\/p>\n\n\n
ALTER<\/span> LOGIN login_name \nWITH<\/span> NAME<\/span> = new_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement changes the login bobcat<\/code> to lion<\/code>:<\/p>\n\n\n
ALTER<\/span> LOGIN bobcat\nWITH<\/span> NAME<\/span> = lion;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Change the password of a login #<\/a><\/h2>\n\n\n\n
To change the password of a login, you use the ALTER LOGIN ... WITH PASSWORD<\/code> statement:<\/p>\n\n\n
ALTER<\/span> LOGIN login_name\nWITH<\/span> PASSWORD<\/span> = new_password;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement changes the password of the login lion<\/code> to a new one:<\/p>\n\n\n
ALTER<\/span> LOGIN lion\nWITH<\/span> PASSWORD<\/span> = 'Hor$e2022.'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If a login account is currently logged in and you do not have the ALTER ANY LOGIN<\/code> permission, you need to specify the OLD_PASSWORD<\/code> option:<\/p>\n\n\n
ALTER<\/span> LOGIN login_name\nWITH<\/span> PASSWORD<\/span> = new_password \n     OLD_PASSWORD = old_password;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example:<\/p>\n\n\n
ALTER<\/span> LOGIN lion\nWITH<\/span> PASSWORD<\/span> = 'Deer$2022.'<\/span> \n\t OLD_PASSWORD = 'Hor$e2022.'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you migrate the login accounts from a legacy database and want to reuse the old password, you can use the hashed password. For example:<\/p>\n\n\n
ALTER<\/span> LOGIN legacy\nWITH<\/span> PASSWORD<\/span>=0x0200B6E66AFC7FF8B4EBCB553B3F95C4A566E724CC2C6265C0C2663DA89C96C38B230C2468DC46E11A3AA32522D3E074D91D9C5A32A9C8535A9DCF3EB49AB233E340C2345EF7<\/span>\nHASHED;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Unlock a login #<\/a><\/h2>\n\n\n\n
If you enforce a password policy on a login and the login account failed a number of times, the login can be locked. <\/p>\n\n\n\n
To unlock a login account, you use the ALTER LOGIN ... UNLOCK<\/code> statement:<\/p>\n\n\n
ALTER<\/span> LOGIN login_name\nWITH<\/span> PASSWORD<\/span>=password<\/span>\nUNLOCK<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example:<\/p>\n\n\n
ALTER<\/span> LOGIN lion\nWITH<\/span> PASSWORD<\/span>='Deer$2023.'<\/span>\nUNLOCK<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
  • Use the ALTER LOGIN<\/code> statement to change the properties of a login account.<\/li><\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t