patternsqlMinor
Handle expired SQL login's passwords in SQL Server
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
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') >= 2Code 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') >= 2Context
StackExchange Database Administrators Q#130018, answer score: 6
Revisions (0)
No revisions yet.