Handle expired SQL login's passwords in SQL Server

Question

Is it possible to change the password for a login with an expired password using SQL Server Management Objects (SMO) programming?

Many times during working hours I got this message from a production server. I have a 'sa' level privilege in SQL Server.

Screenshot

Working Environment

1. Microsoft SQL Server 2012 Express
2. Windows Server 2012 R2
3. VMware,Inc. VMware virtual platform

• Julien Vavasseur: are those password used by users? why are they changing so many times a day ? Perhaps you should use windows auth with AD and groups/roles
• user507: Basic answer: Yes you can use SMO to change a password for a SQL Login.

Answer 1

You can use Login.ChangePassword Method:

public void ChangePassword(
 string newPassword,
 bool unlock,
 bool mustChange
)

You can also change the password using T-SQL:

USE [master]
GO
ALTER LOGIN [test] WITH PASSWORD=N'123456'
GO

Maximum password age can be set:

• Open Administrative Tools / Local Security Policy (or run SECPOL.MSC).
• Go to Security Settings / Account Policies / Password Policy
• Update Maximum Password Age

It can also be set by AD and GPO.

• Md Haidar Ali Khan: Through TSQL i am able to change password.Even i an only able to execute TSQL through SSMS. So, i am accept as answer. Thanks
• Julien Vavasseur: Perhaps there is a better solution iff you add more information and details. It's unclear why only SSMS works and what is the real problem when you are trying to change it.
• Md Haidar Ali Khan: Yes exactly what i had said, only i am able to execute TSQL through 'New Query' window in SSMS. Not able to right click to any folder of SQL Server instance. It's Live production Server.
• Julien Vavasseur: Perfect. TSQL is the right way to do it. You should avoid clicks on Prod.
• Md Haidar Ali Khan: Really you are awesome. +1 for that

Answer 2

Complementing to Julien's answer, you can be proactive to schedule below tsql as a job to alert you on daily basis especially on DaysUntilExpiration of LOGINPROPERTY e.g. get all the logins from your database server that are going to expire in 2 days.

SELECT name as LoginName,
create_date as LoginCreateDate,
modify_date as LoginModifiedDate,
is_policy_checked ,
is_expiration_checked ,
-- returns the number of days until the password expires.
LOGINPROPERTY(name, 'DaysUntilExpiration') DaysUntilExpiration,
LOGINPROPERTY(name, 'PasswordLastSetTime') PasswordLastSetTime,
LOGINPROPERTY(name, 'IsExpired') IsExpired,
LOGINPROPERTY(name, 'IsMustChange') IsMustChange
From sys.sql_logins
--- optional filter 
-- where LOGINPROPERTY(name, 'DaysUntilExpiration') >= 2

• Md Haidar Ali Khan: It's a really good script for my environment.For that +1