TSQL – Change/Alter password for a Login
One of the security policies from Network Security department of any IT organization are to change the password for a SQL server login (or a Windows AD account) once in a year or 6 months.
With respect to SQL server, if the password for a Login named ‘user4RTWP’ has expired after the stipulated time set by the network policies, and when you try to connect to the SQL server using SQL Server Management Studio with the expired password (for the first time after the password has expired), then the SQL Server Management Studio will prompt you with a change password dialog and you could change the password that way.
However, for all our applications using SQL server authentication (to connect to a database to do their jobs) to work properly, we would plan to change the SQL Login password on a specified data before the expiry. This typically involves a downtime from an application’s perspective because
When you have changed the password for a SQL Login, then the applications using that SQL Login would obviously fail with the famous ‘Login Failed’.
Later you would have to update the application’s config entries with the new password.
In case you wanted to avoid a downtime, it is possible when your database servers are load balanced via clusters. And your application is load balanced via a web farm.
While updating the password for a Login in a database, redirect all the database requests to another database server in the cluster. Once done, you can switch back to the original database server in the cluster.
Post which, you could stop a particular instance of the application in the web farm, and redirect all the user requests to other servers in the web farm. Once done, you can switch back to the original application server.
Though this might seem like it doesn’t require a downtime, some user sessions and transactions might be interrupted. So it is advisable to have a downtime.
Now, the in TSQL there are a couple of options for changing the password for a Login. Let’s take ‘user4RTWP’ as a Login that needs it’s password to be changed.
sp_password – MSDN says this procedure is deprecated and will be removed in the future versions of SQL server. Albeit, the documentation for sp_password is pretty helpful than compared to the documentation for ALTER LOGIN, where ALTER LOGIN is the preferred way to change the password for a Login.
You could login to the database server via ‘user4RTWP’ with it’s original password (or old password) ‘passwordisabouttoExPiReS00n’ and execute the ALTER LOGIN command as below
This command would work if ‘user4RTWP’ had CONTROL SERVER permissions or a part of sysadmin role. Otherwise is going to throw
“Msg 15151, Level 16, State 1, Line 1
Cannot alter the login ‘user’, because it does not exist or you do not have permission.”
Normally, only the database administrator has that kind of permissions, but a regular SQL Login that is created for the application and developers to use does not have those permissions. Now you are mandated to run the TSQL below, which will run just fine even if you don’t have CONTROL SERVER permissions.
In case you wanted to change the password for ‘user4RTWP’ login under the identity of another Login called ‘user4AppX’ , then you would need ALTER ANY LOGIN permissions on ‘user4AppX’.
Hoof, I have been changing these password quite a bit these days.