How to get the password change date in SQL Server

I have searched this topic on google first, and I understood that "xdate2
column of sysxlogins" may store this info, but might not exclusively store
this info... this value changes as extra permissions is granted.
Is there a way to get the password change date? Either within a column of a
table, or programatically?
Thank you in advance.
/SMAC
Actually if you are using SQL Server 2005 you can use
select name,modify_date from sys.server_principals
However ,there is no guarantee that only password has been changed , could
be name as well
"SMAC" <info@.smactool.com> wrote in message
news:rVsQg.340$gE7.320@.newsfe05.lga...
>I have searched this topic on google first, and I understood that "xdate2
>column of sysxlogins" may store this info, but might not exclusively store
>this info... this value changes as extra permissions is granted.
> Is there a way to get the password change date? Either within a column of
> a table, or programatically?
> Thank you in advance.
> /
>|||Thank you Uri,
Any sure way to do this for all SQL 7, 2000, 2005?
Thanks,
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
> SMAC
> Actually if you are using SQL Server 2005 you can use
> select name,modify_date from sys.server_principals
>
> However ,there is no guarantee that only password has been changed , could
> be name as well
>
>
>
> "SMAC" <info@.smactool.com> wrote in message
> news:rVsQg.340$gE7.320@.newsfe05.lga...
>|||Check out the syslogins table (view on 2005).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SMAC" <info@.smactool.com> wrote in message news:j2wQg.5$v14.4@.newsfe02.lga...n">
> Thank you Uri,
> Any sure way to do this for all SQL 7, 2000, 2005?
> Thanks,
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uj94vFW3GHA.4648@.TK2MSFTNGP04.phx.gbl...
>|||In 2005 there is loginproperties I can use, how about SQL 7 and 2000? Any
idea?
Thank you.
/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> Check out the syslogins table (view on 2005).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SMAC" <info@.smactool.com> wrote in message
> news:j2wQg.5$v14.4@.newsfe02.lga...|||No, that function doesn't exist in earlier versions. I'd go for syslogins on
pre-2005 and the
LOGINPROPERTY function on 2005 (version check in your code).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SMAC" <info@.smactool.com> wrote in message news:rSIQg.73$c86.46@.newsfe07.lga...een">
> In 2005 there is loginproperties I can use, how about SQL 7 and 2000? Any
idea?
> Thank you.
> /
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:u9WqBDb3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>|||Thanks Tibor,
syslogins doesn't give the reliable password modified date because any
changes to the logins will change the date within syslogins / sysxlogins...
Maybe I'll ask this question in a different way...
when CHECK_EXPIRATION property of the login is set to ON, how does SQL
Server check the password expiration? I believe there is a place where it
stores password creation date right?
Please share your insight.
Thanks!
/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:elCVDFh3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> No, that function doesn't exist in earlier versions. I'd go for syslogins
> on pre-2005 and the LOGINPROPERTY function on 2005 (version check in your
> code).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SMAC" <info@.smactool.com> wrote in message
> news:rSIQg.73$c86.46@.newsfe07.lga...
>|||Most probably you cannot differentiate between password change and other log
in modifications in 2000
and earlier. So for those versions, syslogins is what you have.
As for 2005, where is the problem? Why not use the LOGINPROPERTY() function,
which you already found
and according to the documentation can answer when password was last changed
? Btw, it seems like the
information from that function comes from the same place as the modified_dat
e in sys.sql_logins (as
this doesn't change when I alter the default database for a login...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SMAC" <info@.smactool.com> wrote in message news:6dQQg.310$aF4.164@.newsfe06.lga...green">
> Thanks Tibor,
> syslogins doesn't give the reliable password modified date because any cha
nges to the logins will
> change the date within syslogins / sysxlogins...
> Maybe I'll ask this question in a different way...
> when CHECK_EXPIRATION property of the login is set to ON, how does SQL Ser
ver check the password
> expiration? I believe there is a place where it stores password creation
date right?
> Please share your insight.
> Thanks!
> /
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:elCVDFh3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>|||I believe this is the field that you are looking for:
select xdate2 AS password_change_date,
DATEDIFF(dd, xdate2, GETDATE()) AS days_since_change
FROM master.dbo.sysxlogins
Tibor Karaszi wrote:[vbcol=seagreen]
> Most probably you cannot differentiate between password change and other l
ogin modifications in 2000
> and earlier. So for those versions, syslogins is what you have.
> As for 2005, where is the problem? Why not use the LOGINPROPERTY() functio
n, which you already found
> and according to the documentation can answer when password was last chang
ed? Btw, it seems like the
> information from that function comes from the same place as the modified_d
ate in sys.sql_logins (as
> this doesn't change when I alter the default database for a login...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SMAC" <info@.smactool.com> wrote in message news:6dQQg.310$aF4.164@.newsfe0
6.lga...sql