HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Handle expired SQL login's passwords in SQL Server

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
handlesqlexpiredloginpasswordsserver

Problem

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.

Working Environment

  • Microsoft SQL Server 2012 Express



  • Windows Server 2012 R2



  • VMware,Inc. VMware virtual platform

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#130018, answer score: 6

Revisions (0)

No revisions yet.