September 14, 2017 at 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
September 14, 2017 at 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;
September 14, 2017 at 7:50 am
caz100 - Thursday, September 14, 2017 7:43 AMHi 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)
September 14, 2017 at 7:56 am
caz100 - Thursday, September 14, 2017 7:50 AMI 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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply