SQL Server implements two types of authentication: Windows Authentication and SQL Authentication. Windows Authentication is often also called Integrated Security. Which authentication type is applied is dependent on the server principal used to log on. If the server principal in SQL Server is based on a Windows account or a Windows group, windows authentication has to be used. If the login is a SQL Login, SQL Authentication is applied. With windows Authentication, SQL Server delegates the actual authentication process to windows without ever touching a password. With SQL Authentication SQL Server has to check the password itself. That means that a SQL Login always has to have an associated password stored within SQL Server (if you want to be able to use it to log on).
Today I am going to look at, how you can change that password.
How to use ALTER LOGIN to Change a Password
To change the password of a SQL Login, the ALTER LOGIN command can be used. To demonstrate, we first need to create a login:
CREATE LOGIN ALogin WITH PASSWORD = 'Passw0rd!';
The syntax of the ALTER LOGIN statement is very similar to the CREATE LOGIN statement. To change the password of our login we can just replace the CREATE keyword with ALTER:
ALTER LOGIN ALogin WITH PASSWORD = '2Secr@s';
The following screenshot shows that in action:
SQL Server stores the password as a salted hash, so we cannot just look at the stored password. However, we can see that hash value. The screenshot shows the before and after value to demonstrate that the password did indeed change.
Changing Your Own Password
To change the password of any SQL Login on the server, you need to have been granted the ALTER ANY LOGIN server privilege. However, you would expect that you could change your own password. Let us try:
EXECUTE AS LOGIN = 'ALogin';
ALTER LOGIN ALogin WITH PASSWORD = 'Se6ure?';
Executing this fails:
The reason is that in this context SQL Server requires the old password to also be specified, like this:
ALTER LOGIN ALogin WITH PASSWORD = 'Se6ure?' OLD_PASSWORD = '2Secr@s';
That now leads to a successful password change:
While this works, it feels a little odd. If I have authenticated using my password already, why do I need to re-authenticate for a password change again? The reason is that Microsoft wanted to prevent principals that just have the IMPERSONATE privilege on a login to be able to change the password for that login. In the example above I used EXECUTE AS and did not anywhere supply the password.
As every login has control permission on itself, that privilege alone cannot be enough to change a login's password. Therefore you have to specify the current password if you want to change your own password. You actually can change any login's password, as long as you have the CONTROL orALTER privilege on that login and you now its current password.
If you on the other hand have been granted the ALTER ANY LOGIN server privilege, you can change any login's password without specifying the current one, even if you change your own:
Changing the Password for a Windows Login
In the case of a windows login, SQL Server delegates the authentication to Windows. SQL Server never gets to store or even see a password. That means you cannot change a Windows account password from within SQL Server. If you tried to use ALTER LOGIN ... WITH PASSWORD on a windows login, you would get this error message:
Msg 15080, Level 16, State 1, Line 1
Cannot use parameter PASSWORD for a Windows login.
To actually change the password of a windows account, you have to use any documented windows method.
To change the password of the SQL Login in SQL Server you can use the ALTER LOGIN ... WITH PASSWORD command. To be able to change the password of any SQL Login you must have been granted the ALTER ANY LOGIN server privilege. However, you always can change your own password by specifying the OLD_PASSWORD.