SQL query to find logins where LOGINPROPERTY(name, 'PasswordLastSetTime') is less than 2017-01-01

Question

SQL query to find logins where LOGINPROPERTY(name, 'PasswordLastSetTime') is less than 2017-01-01

caz100

SSCommitted

Points: 1827
More actions
September 14, 2017 at 7:43 am

#325235

Hi I have a query which shows logins details including among other things last password set time as follows:
Select @@servername as SQLInstance, name as Login,
LOGINPROPERTY(name, 'PasswordLastSetTime') as PasswordLastSetTime, type, type_desc, is_disabled, create_date, modify_date
from sys.server_principals
where Type IN ('S')
and name not like '%##%'
and is_disabled=0
--and LOGINPROPERTY(name, 'PasswordLastSetTime') < (2017-01-01)
order by name;
The -- commented out line is not working. I would like to only include logins that have NOT changed their passwords in 2017.
Can someone help with that sql code?
Thanks in Advance

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

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

caz100 SSCommitted Points: 1827 More actions · Answer 1

I figured it out....using convert datetime works perfect so:

Select @@servername as SQLInstance, name as Login,
LOGINPROPERTY(name, 'PasswordLastSetTime') as PasswordLastSetTime, type, type_desc, is_disabled, create_date, modify_date
from sys.server_principals
where Type IN ('S')
and name not like '%##%'
and is_disabled=0
and LOGINPROPERTY(name, 'PasswordLastSetTime') < convert(datetime, '2017-01-01')
order by name;

sgmunson SSC Guru Points: 110640 More actions · Answer 2

caz100 - Thursday, September 14, 2017 7:43 AM
Hi I have a query which shows logins details including among other things last password set time as follows:
Select @@servername as SQLInstance, name as Login,
LOGINPROPERTY(name, 'PasswordLastSetTime') as PasswordLastSetTime, type, type_desc, is_disabled, create_date, modify_date
from sys.server_principals
where Type IN ('S')
and name not like '%##%'
and is_disabled=0
--and LOGINPROPERTY(name, 'PasswordLastSetTime') < (2017-01-01)
order by name;
The -- commented out line is not working. I would like to only include logins that have NOT changed their passwords in 2017.
Can someone help with that sql code?
Thanks in Advance

Try this small change:
Select @@servername as SQLInstance, name as Login, LOGINPROPERTY(name, 'PasswordLastSetTime') as PasswordLastSetTime, type, type_desc, is_disabled, create_date, modify_date from sys.server_principals where Type IN ('S') and name not like '%##%' and is_disabled=0 and LOGINPROPERTY(name, 'PasswordLastSetTime') < '2017-01-01'

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

caz100 - Thursday, September 14, 2017 7:50 AM
I figured it out....using convert datetime works perfect so:
Select @@servername as SQLInstance, name as Login,
LOGINPROPERTY(name, 'PasswordLastSetTime') as PasswordLastSetTime, type, type_desc, is_disabled, create_date, modify_date
from sys.server_principals
where Type IN ('S')
and name not like '%##%'
and is_disabled=0
and LOGINPROPERTY(name, 'PasswordLastSetTime') < convert(datetime, '2017-01-01')
order by name;

Yes it's down to type mismatching, LOGINPROPERTY gives a sql_variant data type output so need to do an explicit conversion of the LOGINPROPERTY or the WHERE predicate as you have done to ensure that conversion happens correctly