October 2, 2009 at 5:28 am
Good Morning...
I need to review the logins in all my 15 servers running instances of SQL SERVER (2000 and 2005).
Most of logins here, use the "Mixed Mode Authentication", and lots of logins in Active Directory have been disabled or deleted.
I'd like to know how I can get a list of these logins, that have been disabled or deleted on Active Directory.
or / and
to get a list of logins that didn't log on to SQL SERVER for a period longer than 30 days...
Thank you very much
att
Edvaldo Castro.
October 2, 2009 at 2:46 pm
SQL Serer does not keep track of when a login last connected. You'd have to come up with a different way to track that (logon trigger, trace, etc.). As far as checking to see if accounts exist any longer in AD:
sp_validatelogins
K. Brian Kelley
@kbriankelley
October 2, 2009 at 3:27 pm
Active Directory itself includes a attribute that can be queried that has the last logon timestamp.
Now this timestamp does not mean that SQL Server was the last thing they logged onto, just the last time the username was authenticated on the domain. I know there are restrictions on when the timestamp is registered with AD as to what they logged onto, this post has a good explanation.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 2, 2009 at 5:41 pm
Melton (10/2/2009)
Active Directory itself includes a attribute that can be queried that has the last logon timestamp.Now this timestamp does not mean that SQL Server was the last thing they logged onto, just the last time the username was authenticated on the domain. I know there are restrictions on when the timestamp is registered with AD as to what they logged onto, this post has a good explanation.
Generally we don't rely on this because of the fact that not everything updates the timestamp as well as the fact that it's replicated so infrequently. When we have to pull last login to satisfy audit requirements, we end up querying every DC and taking the most recent value.
K. Brian Kelley
@kbriankelley
October 2, 2009 at 9:06 pm
Hello Everybody...
I got my problem solved by using the stored procedure sp_validatelogins
Thank you very much
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply