In my last post I hoped to convince you to pay attention to all of the various “Login Failed for user…” messages that you see in your SQL Server ERRORLOGS. ALL of them.
Yes, some you can ignore based on the environment or the person. Jim the web guy on a Dev box is just not that much of a security threat (unless you let him touch Prod, but that’s a different post).
Some of you have one or two servers, and reviewing ERRORLOGs is no big deal to do manually. More of you have tens and tens of them. Some of you have thousands (I’m looking at you in Managed Hosting environments such as Verizon, Rackspace, etc. where customers pay you to do this).
By now, hopefully you are aware that you can issue queries against all servers or groups of servers at once. If not, a very quick how-to in SSMS:
- View-registered servers (Ctrl-ALT-G)
- Expand Database Engine
- Right-Click Local Server Groups
- Set up groups as you see fit (mine are Prod and non-prod)
- Register servers in the appropriate group
To query a group, right-click the group (or Local Server Groups for all of them), select New Query and off you go. The results will append the server name as the first column.
Note that my servers are listed in order, but the results are not. Results come in the order SQL Server was able to connect and receive the results back:
Side note…running Select @@version is a great way to ensure all of the SQL Servers are up and running, especially after a Patch weekend, or even just first thing in the morning.
Now for the stuff you actually wanted to read/learn…how to read the ERRORLOGS all at once:
We are going to dump the results of sys.xp_readerrorlog into a temp table, and then query just like any other table:
Create Table #Errorlog
–Dump all the things into the table
insert into #Errorlog
0 — Current ERRORLOG
,1 — SQL ERRORLOG (not Agent)
–Query just like you would anything else:
and (LogText like ‘%Error%’
or LogText like ‘%Fail%’)
And Logdate > getdate() -3
–Clean up your mess, you weren’t raised in a barn!
Drop Table #Errorlog
If I’m doing this on a Monday, I set the date to look back to over the weekend…otherwise 1 or 2 days. But whatever works best for you. There is nothing magic in here. For more details on xp_readerrorlog, click the link. No point in me re-writing a perfectly good explanation.
Hopefully this will help you pay more attention to what’s going on in your ERRORLOGs, whether for Login Failed, or even just to find all backups for a certain DB. Just change the Where clause to fit your needs.
If I saved you any time at all, I’ve done my job. Let me know if you actually use this!
Fries are awesome, but I’m trying to ShrinkFile my gut…
Johannes Groiss says
Nice Solution. You can also use a SQL Policy to track wrong logons in your Environment … combined with SQL EPM it is a powerfull tool for the dba.