June 27, 2003 at 12:38 pm
Does SQL Server keep track of the date in which a login last connected to the server? We have a server with over 1000 SQL logins, and many of these are probably no longer needed.
Thanks for your assistance
June 27, 2003 at 1:10 pm
Don't think SQL Server does that. You may temporary deny database access to those logins you think they no longer need and see whether users complain about.
June 27, 2003 at 1:32 pm
quote:
Don't think SQL Server does that. You may temporary deny database access to those logins you think they no longer need and see whether users complain about.
Allen,
Check this web site:
http://documents.iss.net/literature/DatabaseScanner/reports/sql/SQLStaleLog.pdf
They have a document that list stale Logins which is exactly what I am looking for, what they don't provide is how they determined what was a stale login and then produce a report...
Thanks
June 27, 2003 at 1:42 pm
No ideas how they produce the report. They might trace the logins by examining transaction log or using Profiler to trace who are accessing SQL Server and create histroy logs for user login.
June 27, 2003 at 1:47 pm
quote:
No ideas how they produce the report. They might trace the logins by examining transaction log or using Profiler to trace who are accessing SQL Server and create histroy logs for user login.
I was looking at System tables and thinking the same thing...Maybe a good idea for creating a script that will capture this information. I would be willing to bet many DBA's run into this problem when starting a new job...
Muchas Gracias y Thanks
June 27, 2003 at 2:07 pm
I think ISS reads the ERRORLOG file looking for "Login succeeded for user" messages to keep track of the last time a user logged in. Anyone could just do this, but first you need to turn on auditing of successful logins.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
June 27, 2003 at 2:52 pm
quote:
I think ISS reads the ERRORLOG file looking for "Login succeeded for user" messages to keep track of the last time a user logged in. Anyone could just do this, but first you need to turn on auditing of successful logins.Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
I was hoping SQL stored this info in a table would have been a lot easier but looks like I am out of luck. I have modified auditing to capture both successful and failed logins (failed so I can see who may be trying to get in that shouldn't).
Thanks for pointing me in the right direction...
June 27, 2003 at 5:04 pm
You might consider using the undocumented extended SP "xp_readerrorlog" for reading the ERRORLOG file.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply