SQL Server Login Audit

Hi All,

I really very thankful to all members of SSC. Well I have come across a situation... I want to track all the INVALID LOGINS into SQL Server Database. If someone is attempting to login to SQL Server with incorrect Login Details then a TRIGGER should be fired to INSERT that details into one Database Table.

Please help me to resolve this out.

Thanks Again...

more ▼

asked Feb 25 '10 at 11:18 AM in Default

Prashant gravatar image

1 1 1 1

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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

more ▼

answered Feb 25 '10 at 11:27 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

(comments are locked)
10|1200 characters needed characters left

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/

more ▼

answered Feb 25 '10 at 01:05 PM

CirqueDeSQLeil gravatar image

4k 10 11 15

(comments are locked)
10|1200 characters needed characters left

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!

more ▼

answered Feb 25 '10 at 04:21 PM

Mansour Shoari gravatar image

Mansour Shoari
435 13 14 15

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 25 '10 at 11:30 PM

philcart gravatar image


(comments are locked)
10|1200 characters needed characters left


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.
more ▼

answered Apr 16 '13 at 07:06 AM

RichardJenson gravatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 25 '10 at 11:18 AM

Seen: 3424 times

Last Updated: Apr 16 '13 at 07:06 AM