How to enable both failed and Successful login in SQL Server auditing

How to enable both failed and Successful login in SQL Server auditing

• It requires RESTART of SQL Service
• By Default Failed Logins Only enabled in SQL SERVER

By Default Failed Logins Only enabled in SQL SERVER But we need enable both failed and Successful logins  but it requires restart



Here Now I am going to choose both failed and successful login and enable C2 auditing

After click ok it asks for restart to take effect of this features

Now am going to restart of SQL Service

after Restart of SQL Service  you can see take effect in Error log as both failed and successful logins

you can  see error log file will grow based on the login flow ( Error log file count 6 )

TSQL Query for cycle error log and monitor Error log size and location

you can cycle the error log so it will create a new one with the following command

Exec Sp_Cycle_Errorlog

 

--Monitor Error log size and location

 

Declare @ServerErrorLog TABLE

(

Archive INT,

dt DATETIME,

FileSize INT

)

 

INSERT INTO @ServerErrorLog

Exec xp_enumerrorlogs

SELECT @@servername as ServerName,Archive, FileSize/1024/1024 AS[Error Log Filef size (MB)],SUM(FileSize/1024/1024)[Size in MB]

FROM @ServerErrorLog

Group by Archive,FileSize/1024/1024

order by Archive,FileSize/1024/1024

COMPUTE SUM(FileSize/1024/1024)

 

 

Exec xp_readerrorlog 0, 1, N'Logging SQL Server messages in file',NULL, NULL, N'asc'

Happy Blogging!!!!!!!!!!!!!!!!!!!!!!