4

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
2
  • 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 Feb 22, 2016 at 9:01
  • 1
    Basic answer: Yes you can use SMO to change a password for a SQL Login.
    – user507
    Feb 22, 2016 at 9:22

2 Answers 2

4

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.

5
  • 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 Feb 22, 2016 at 10:11
  • 1
    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. Feb 22, 2016 at 10:14
  • 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. Feb 22, 2016 at 10:24
  • 1
    Perfect. TSQL is the right way to do it. You should avoid clicks on Prod. Feb 22, 2016 at 10:25
  • Really you are awesome. +1 for that Feb 22, 2016 at 10:26
6

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
1
  • It's a really good script for my environment.For that +1 Feb 24, 2016 at 5:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.