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
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)
4 Comments. Leave new
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
Braj
Where is the SQL Login name in query result? Why that column name isn’t added to the query? any reason?
put [Name] in the select list