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)