July 21, 2011 at 6:22 am
Thomas Abraham (7/21/2011)
I tried it on a backup server (2008). Didn't want to cycle the server, so I chose a user that was set up, but for which I knew they had not logged into the system. When I run:SELECT loginproperty('someuser', 'badpasswordcount')
SELECT loginproperty('someuser', 'badpasswordtime')
(where 'someuser' is the login name of an actual user)
I get NULL for both.
Did you change the security setting to mixed mode prior to running the code? I suspect that's the clue. When the security mode is changed, it converts the Null to the 1900-01-01.
July 21, 2011 at 6:24 am
Ok, mine is in mixed mode when I run it. Maybe that is the difference.
July 21, 2011 at 6:25 am
cengland0 (7/21/2011)
Did you change the security setting to mixed mode prior to running the code? I suspect that's the clue. When the security mode is changed, it converts the Null to the 1900-01-01.
Yes, I did change the mode.
Edited later: BUT, I didn't cycle the serve after the mode change. Does that make a difference?
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
July 21, 2011 at 6:32 am
Thomas Abraham (7/21/2011)
Yes, I did change the mode.Edited later: BUT, I didn't cycle the serve after the mode change. Does that make a difference?
So to be sure, you had it on "Windows Authentication" and changed it to "Mixed Mode" and then immediately ran the query and got Null? And you're on SQL Server 2008. What about doing that with the sa account? I believe you picked a user that has never logged in so maybe that's different on the sa account which probably has been logged in at least once.
July 21, 2011 at 6:37 am
cengland0 (7/21/2011)
Thomas Abraham (7/21/2011)
Yes, I did change the mode.Edited later: BUT, I didn't cycle the serve after the mode change. Does that make a difference?
So to be sure, you had it on "Windows Authentication" and changed it to "Mixed Mode" and then immediately ran the query and got Null? And you're on SQL Server 2008. What about doing that with the sa account? I believe you picked a user that has never logged in so maybe that's different on the sa account which probably has been logged in at least once.
Steve specifically says that the server is a new install in windows mode which would make me assume that SA has never,ever logged in.
July 21, 2011 at 6:48 am
Ninja's_RGR'us (7/21/2011)
Steve specifically says that the server is a new install in windows mode which would make me assume that SA has never,ever logged in.
He also said the server was used for four months before the security mode was changed. Then he says, in the explaination:
"The default for this property is 1900-01-01 on a server if there has never been a failed login due to an incorrect password."
So, that's why I checked using a user name that was set up, but where the user login had not been used.
As for an ISNULL() check setting it to 1900-01-01, why doesn't the call return that value when you enter a bogus user name?
I'm not saying that NULL is the correct answer. But, given that the referene cited is silent on the default value, and the results we've seen so far, I don't see how 1900-01-01 is the answer here?
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
July 21, 2011 at 6:50 am
The question does specifically say SQL208 and for sa.
For any SQL authenticated account I get 1900-01-01, for windows accounts I get NULL
---------------------------------------------------------------------
July 21, 2011 at 6:50 am
Bottom live fore me is that this is a nive reminder of that function. I frankly don't care wether I get NULL vs 1900-01-01 when running it. :w00t:
July 21, 2011 at 7:02 am
george sibbald (7/21/2011)
The question does specifically say SQL208 and for sa.For any SQL authenticated account I get 1900-01-01, for windows accounts I get NULL
That goes with what I've seen in further testing. For example, I get 0 back from a test for sa badlogincount, but NULL back for Windows authenticated users. Weird, but inconsistencies like that are not unknown in SQL Server.
Thanks for the question Steve.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
July 21, 2011 at 7:05 am
Nice clear easy question with a clear easy answer.
Thanks Steve 😎
July 21, 2011 at 7:07 am
Ninja's_RGR'us (7/21/2011)
paul s-306273 (7/21/2011)
I'm surprised that the answer isn't null.Can somebody prove or disprove that.
(I don't have the facility to do this myself - sorry!)
I'll add to this. I know I'm "late" but I'm still on 2k5 and that gave me NULL.
I tried the same thing on 2k8 and it gave me 1900-01-01.
Can anyone else confirm this or was my test wrong?
I have a SQL 2008 server in our QA/DEV environment that was changed from Windows only to Mixed mode. It is exactly '1900-01-01 00:00:00.000'
July 21, 2011 at 7:10 am
Thanks SanDroid but my question also included a test on Sql 2K5 ;-).
July 21, 2011 at 7:12 am
Ninja's_RGR'us (7/21/2011)
Thanks SanDroid but my question also included a test on Sql 2K5 ;-).
My answer did not. And nither did the QOTD. 😛
July 21, 2011 at 7:15 am
SanDroid (7/21/2011)
Ninja's_RGR'us (7/21/2011)
Thanks SanDroid but my question also included a test on Sql 2K5 ;-).My answer did not. And nither did the QOTD. 😛
I know, MY question did :-D.
July 21, 2011 at 7:28 am
Ninja's_RGR'us (7/21/2011)
SanDroid (7/21/2011)
Ninja's_RGR'us (7/21/2011)
Thanks SanDroid but my question also included a test on Sql 2K5 ;-).My answer did not. And nither did the QOTD. 😛
I know, MY question did :-D.
Good for you! :w00t: Have a nice day! 😎
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply