Is there a way to get advance warning of upcoming SQL Server login password expirations?

Question

Is there a way to get advance warning of upcoming SQL Server login password expirations?

shew

SSChampion

Points: 11389
More actions
June 22, 2010 at 6:55 am

#234086

Is there a way to get advance warning of upcoming SQL Server 2008 (and 2005) login password expirations?
Ideally, I want to form a query that I can put in a nightly script that generates an email warning about upcoming password expirations. I can handle the email piece of it, but I need help forming the appropriate query.
Here is the code for my "test" login (scripted from SQL Server Management Studio on 2005):
CREATE LOGIN [jps] WITH PASSWORD=N'*************',
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
This is as close as I have gotten, but DaysUntilExpiration does not populate:
select
name,
loginproperty (name, 'IsExpired') as IsExpired,
loginproperty (name, 'DaysUntilExpiration') as DaysUntilExpiration,
loginproperty (name, 'PasswordLastSetTime') as PasswordLastSetTime,
loginproperty (name, 'HistoryLength') as HistoryLength
from
master.sys.server_principals
where
name='jps'
order by
name
name IsExpired DaysUntilExpiration PasswordLastSetTime HistoryLength
---- --------- ------------------- ----------------------- -------------
jps 0 NULL 2010-06-22 08:07:40.317 1
(1 row(s) affected)
The above query ran on SQL Server 2005. However, DaysUntilExpiration does not populate because it is not a supported feature of 2005. (It does *NOT* error out, it just gets ignored.)
Any ideas?
I need to do this for 2008 as well. Does SQL Server 2008 work in a similar fashion?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

shew SSChampion Points: 11389 More actions · Answer 1

I found some additional information at http://searchsqlserver.techtarget.com/news/1102101/SQL-Server-password-policies-and-credentials, but I am not sure how to use it.

If I am reading correctly, SQL Server will use local policy setting for the maximum password age. On this server, I see that maximum password age is set to 60 days. (Open gpedit.msc, and drill down to Computer Configuration\Windows Settings\Security Settings\Account Policies\Password Policy\Maximum password age.)

Am I looking in the correct location for the value that SQL Server will use?

fcastellano Valued Member Points: 71 More actions · Answer 2

I have the same issue, please let me know if you get a way to solve this problem (SQL 2005)

shew SSChampion Points: 11389 More actions · Answer 3

I never found an easy way to do it. I started working on some code to do it, but I got sidetracked on other tasks and never finished it.