Trending SQL Server Tips

Auditing SQL Server Logins

March 08, 2010

Page: 1/2


During the recent internal auditing of our backend SQL Server, which is being used by the Finance team; it was identified that there is no auditing enabled for the SQL Server Logins. As the Finance backend SQL Server hosts organization wide critical data, it is mandatory to audit both failed and successful logins. In this article we will take a look at how a database administrator can easily enable auditing in SQL Server for logins. The steps mentioned below are applicable on SQL Server 2005 and higher versions.

"This article has a total of 2 pages including this page. Click the links below to open all 2 pages in a seperate window to learn Auditing SQL Server Loginsin SQL Server. Page 1, Page 2"

1. Connect to the SQL Server 2005 Instance using SQL Server Management Studio and then right click the SQL Server Instance within the Object Explorer and choose Properties option from the pop-up menu as shown in the below snippet.

2. In Server Properties navigate to Security page as highlighted below snippet. Under Logging Auditing select the last option which is Both failed and successful logins and click OK to save the configuration changes.

There are four types of login auditing available in SQL Server:-

None – No logins will be audited i.e. neither successful nor any failed logins will be audited.

Failed logins only – Only failed logins will be audited, and at the same time all the all the successfully logins will be ignored.

Successful logins only – Only successful logins will be audited and at the same time all the failed logins will be ignored

Both failed and successful logins – Both failed and successfully logins will be audited.

In our case as the SQL Server host organization wide critical financial information, we had no choose but to enable both failed and successful logins so that we can monitor each and every single activity on the SQL Server.

Best Practices: Database Administrator should always make sure that they are auditing at least failed logins across production servers.

Once you have made a choice for the level of login auditing for the Server; click OK to save the change. Since this is a registry level change, in order to bring the new login audit setting into effect you need to restart the SQL Server Database Engine Service using SQL Server Configuration Manager.

Click on Next Page to continue reading rest of the article…

Next Page..

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."