SQL SERVER – How to Find Out When Your SQL Login Will Expire?

If you are a SQLDBA, you must have created a login and you must have known about enforcing password policy to SQL Logins. Once of my client asked to provide script to find expiry details about SQL Login.  It was an easy task. I went to books online and used LOGINPROPERTY function and passed name to all the possible parameters. Below would work for SQL 2008 onwards.

SELECT  LOGINPROPERTY(name, 'BadPasswordCount') AS 'BadPasswordCount'
,LOGINPROPERTY(name, 'BadPasswordTime') AS 'BadPasswordTime'
,LOGINPROPERTY(name, 'DaysUntilExpiration') AS 'DaysUntilExpiration'
,LOGINPROPERTY(name, 'DefaultDatabase') AS 'DefaultDatabase'
,LOGINPROPERTY(name, 'DefaultLanguage') AS 'DefaultLanguage'
,LOGINPROPERTY(name, 'HistoryLength') AS 'HistoryLength'
,LOGINPROPERTY(name, 'IsExpired') AS 'IsExpired'
,LOGINPROPERTY(name, 'IsLocked') AS 'IsLocked'
,LOGINPROPERTY(name, 'IsMustChange') AS 'IsMustChange'
,LOGINPROPERTY(name, 'LockoutTime') AS 'LockoutTime'
,LOGINPROPERTY(name, 'PasswordHash') AS 'PasswordHash'
,LOGINPROPERTY(name, 'PasswordLastSetTime') AS 'PasswordLastSetTime'
,LOGINPROPERTY(name, 'PasswordHashAlgorithm') AS 'PasswordHashAlgorithm'
,is_expiration_checked	As 'is_expiration_checked'
FROM    sys.sql_logins
WHERE	is_policy_checked = 1

So far it was easy, but then they asked me to provide the same for Windows Logins. I searched the internet and found command to get details about Windows account using command line.

To identify when the password for Windows account will expire, we need to open a command prompt window and type the following command:

net user <User Name> /domain

SQL SERVER - How to Find Out When Your SQL Login Will Expire? LoginExpiry

The output of the above command will have several lines. As highlighted, there would be a line starts with “Password expires” and we can see the exact day and time when that account’s password will expire. In my sample service account, I have not set any expiry and that’s why we see “Never”. I hope you like this blog post about SQL Login.

Do you know any other way than xp_cmdshell to get the same information? Please share and learn with each other.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, SQL Scripts, SQL Server, SQL Server Security
Previous Post
Identify Used Space in Log File- Interview Question of the Week #090
Next Post
How to Introduce Time Delay Between Two T-SQL Commands? – Interview Question of the Week #091

Related Posts

3 Comments. Leave new

  • Jose María Laguna
    September 27, 2016 4:54 pm

    I use this for Active directory using powershell
    Key is “msDS-UserPasswordExpiryTimeComputed” property. If this property is null then user password do not expire.
    ( lastlogontimestamp is not accurate on 3 last days. )

    $i = ‘user1′,’user2′,’user3’

    $cacudidadpassword= @{n=”cacudidadpassword”;e={[datetime]::FromFileTime($_.”msDS-UserPasswordExpiryTimeComputed”) } }
    $lastlogontimestamp = @{n=”lastlogontimestamp”; e={[datetime]::FromFileTime($_.lastlogontimestamp) } }
    $passwordcambiada = @{n=”passwordcambiada”; e={[datetime]::FromFileTime($_.pwdlastset) } }

    $i | get-aduser -properties “msDS-UserPasswordExpiryTimeComputed”,lastlogontimestamp,pwdlastset | sort “msDS-UserPasswordExpiryTimeComputed” | select samaccountname,$cacudidadpassword,$lastlogontimestamp,$passwordcambiada

  • Hi Pinal,

    How can I get all the Logins who are set to ‘Enforce password expiration’ unchecked on a server?
    I need to know all the logins whose password to expire soon. Any script will help a lot

    Thanks & Regards

  • Where is the SQL Login name in query result? Why that column name isn’t added to the query? any reason?


Leave a Reply