SQL Server Login Audit

This is available as a Server setting and gets logged in the SQL Server Event Log. Go to SSMS and right click your server, choose Properties and then select the Security tab. Select the setting that you want, "Failed Logins only" would match your question most closely

You can use DDL triggers to accomplish this.

One article that might be of help: http://www.simple-talk.com/sql/t-sql-programming/sql-server-2005-ddl-trigger-workbench/

Hi,

If in future you still face such a kind of situation where you want to get the details of all the users who are logging in with incorrect Login details in SQL server, then my approach for you is to try in an affordable tool named LepideAuditor for SQL Server which I have tested and can perform this task for you erroneously. Besides this it can also send you these details to your email id in the CSV, HTML or PDF format through its scheduled report operation.

I am not a Network Administrator/Specialist, but I think SQL logins also leave a footprint in Windows Server Event Viewer (RMC on My Computer > Manage > Event Viewer > Security). You will need to enable recording “Failed Logins” in your Windows Server “Local Security Policy” (Start > Programs > Administrator Tools > Local Security Policy > Local Policies > Audit Policy > Local Security Setting > Audit these attempts: Checkmark in box “Failure”). Could some one with “Network Administrator” experience confirm this, please!

Are you running SQL 2005/2008? If so, then use Event Notifications.

Then you can track when people login with privileged accounts, not just failed logins.