Pages

Wednesday, September 21, 2016

Get SQL Server Custom Login Password expiration Details

 The below code will execute in the SQL server then you will get the details


--Show all logins password expiration date and creation date

SELECT name AS SQLLoginName,
   DATEADD(DAY, CAST(LOGINPROPERTY(name, 'DaysUntilExpiration') AS int), GETDATE())
AS ExpirationDate,
   create_date
   FROM sys.server_principals
   WHERE type = 'S'


-- Show all logins where the password was changed within the last day

SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
FROM sys.sql_logins
WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') > DATEADD(dd, -1, GETDATE());


-- Show all logins where the password is over 60 days old

SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
FROM sys.sql_logins
WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -60, GETDATE())
  AND NOT (LEFT([name], 2) = '##' AND RIGHT([name], 2) = '##');


--Change Password via code

ALTER LOGIN TestDBs
WITH PASSWORD = 'Asdeft##';