I realize this doesn't answer the question "why does the log file have multiple entries for 'Login succeeded...'", however since you also seem to be concerned about the other log entries being "drowned-out" by these low-importance login messages, I thought I'd add the following as an answer.
Instead of using SQL Server Management Studio's built-in interface for looking at log files, you can use the following code that will allow you to ignore log messages you are not interested in. This allows you to concentrate on messages that may be more immediately relevant.
IF NOT EXISTS(
SELECT s.name + '.' + t.name
FROM tempdb.sys.tables t
INNER JOIN tempdb.sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name + '.' + t.name = 'dbo.ErrorLogAnalysis'
)
BEGIN
CREATE TABLE tempdb.dbo.ErrorLogAnalysis
(
LogDate DATETIME,
ProcessInfo NVARCHAR(255),
[Text] NVARCHAR(MAX)
);
END
TRUNCATE TABLE tempdb.dbo.ErrorLogAnalysis;
EXEC sp_ReadErrorLog 0,1;
INSERT INTO tempdb.dbo.ErrorLogAnalysis
SELECT ela.*
FROM tempdb.dbo.ErrorLogAnalysis ela
WHERE ela.Text NOT LIKE 'Login succeeded%'
ORDER BY LogDate DESC;
It may be helpful to point out that client-side code is quite likely to use more than a single connection to SQL Server. This may in fact be the precise reason why more than a single entry is recorded in the SQL Server Error Log.
To prove this, I've written a very simple VB.Net command-line app that creates 5 connections to my local SQL Server instance. Once all five connections have been opened, the app executes a simple query against the server showing 5 session_id
values that the app has open. It then closes all 5 connections, and exits.
This is the code:
Module Module1
Sub Main()
Dim sConnString As String =
"SERVER=localhost;" & _
"DATABASE=master;" & _
"TRUSTED_CONNECTION=True;" & _
"Application Name=MultiConnTest"
Dim sqlC1 As New SqlClient.SqlConnection(sConnString)
Dim sqlC2 As New SqlClient.SqlConnection(sConnString)
Dim sqlC3 As New SqlClient.SqlConnection(sConnString)
Dim sqlC4 As New SqlClient.SqlConnection(sConnString)
Dim sqlC5 As New SqlClient.SqlConnection(sConnString)
sqlC1.Open()
sqlC2.Open()
sqlC3.Open()
sqlC4.Open()
sqlC5.Open()
Dim sqlCMD As New SqlClient.SqlCommand( _
"SELECT session_id, login_name " & _
"FROM sys.dm_exec_sessions des " & _
"WHERE des.host_name = HOST_NAME " & _
" AND des.program_name = 'MultiConnTest';", sqlC1)
Console.WriteLine("Testing multiple connections to SQL Server.")
Console.WriteLine()
Console.WriteLine("Sessions currently open on the server, with username")
Dim sqlReader As SqlClient.SqlDataReader = sqlCMD.ExecuteReader
Dim iSessionID As Int32
Dim sUserName As String
While sqlReader.Read
iSessionID = sqlReader.GetValue(0)
sUserName = sqlReader.GetValue(1)
Console.WriteLine(iSessionID & vbTab & sUserName)
End While
sqlReader.Close()
sqlCMD = Nothing
sqlC1.Close()
sqlC2.Close()
sqlC3.Close()
sqlC4.Close()
sqlC5.Close()
Console.WriteLine("Press any key to exit")
Console.ReadKey()
End Sub
End Module
The console output from running this against my machine is:
Testing multiple connections to SQL Server.
Sessions currently open on the server, with username
52 Max
53 Max
54 Max
55 Max
56 Max
Press any key to exit
The SQL Server Error Log, after enabling auditing of both successful and unsuccessful logins, shows:
LogDate ProcessInfo Text
2014-09-16 10:31:54.290 Logon Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
2014-09-16 10:31:54.290 Logon Login succeeded for user 'Max'. Connection made using Windows authentication. [CLIENT: <local machine>]
I think you can see from this a possible source of your error log entries is not some errant SQL Server behavior that can be "disabled", but is far more likely to be the desired result arising from the combination of auditing successful logins, and client applications that use multiple connections.
I would check the source code of whatever applications are running against your SQL Server to determine if they are in-fact using multiple connections.
If they are, I would use sp_readerrorlog
to look at the error log. I might even go so far as to create a stored procedure to encapsulate the functionality of sp_readerrorlog
to produce output in whatever format you desire.
There are a couple of possibilities.
First if the orphaned user is a windows login/group (type U or G) then no problem.
-- If the login doesn't currently exist on the server
CREATE LOGIN [Windows Account] FROM WINDOWS;
-- Fix the user
USE dbname
ALTER USER [user name] WITH LOGIN = [Windows Account];
If it's a "SQL Login" (type S) and the login exists then again no problem.
-- Fix the user
USE dbname
ALTER USER [user name] WITH LOGIN = [Windows Account];
If the login does not exist for a "SQL Login" then you have to create the login and that will require knowing the password.
-- Create the login
CREATE LOGIN [login name] WITH PASSWORD = '<strong password>';
Don't forget to make sure your password meets your windows policy requirements.
If you still have the old server you can copy the login pretty easily from the other server including the password by getting the HASH of the password from the other server. (There may be some issues depending on if the two servers are different versions).
I did a post on this here: http://sqlstudies.com/2013/03/25/how-do-i-move-a-sql-login-from-one-server-to-another-without-the-password/
Best Answer
Unless you set up something (Server side trace, Profiler trace, Extended event trace, Security audit, Modify SQL Error log retention) and retain that information you will not find what you are looking for.
Based on the comments, I assume what you are looking for will be in the default trace. But unfortunately SQL Server only keeps 5 files, each 20MB. It is a fixed size buffer meaning older events get deleted.
This is a great resource to learn about default trace:
https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server-the-power-of-performance-and-security-auditing/
Starting SQL 2008 Extended event health session is running continuously unless someone turn it off.
SQL error log keeps a good deal of information. Again by default keep current and 6 older files. If you do not recycle error log and did not restart more than 6 times you might still get some information from there.
This answer by Aaron Bertrand go in details about what information is collected in these default collections.
If you need anything more than these you can always set up your own trace/audit.