How to log a partial login history in SQL Server

Question

I'm using SQL Server 2017 Standard.
I want to keep the history of logging in to SQL Server as a log.

I made the following settings from SQL Server Management Studio:
(Right-click the server name) > Properties > Security > Select "Both failed and successful logins" in Login auditing

However, after setting this setting, a large amount of "Sourcs : Logon" is output in SQL Server Log.

For example, after login in with sa user, The log shows a lot of the following messages, even if I log in only once:
Login succeeded for user 'sa'. Connection made using SQL Server authentication.

This message appears to continue to be logged periodically while the connection to the DB server continues.
I don't mind leaving a history anywhere other than SQL Server Log, Is there no way to log only when the login is made?

Answer 1

Hi @knt1N ,

Here are some ways to find the date of the user's last login :

• Using Login Auditing, based on the settings of your enabled ssms, create a script, then create a job to run it periodically and collect the necessary data.
• Using a logon trigger (For example):

creation of history tables

IF (OBJECT_ID('dbo.LoginHistory') IS NULL)  
BEGIN  
-- DROP TABLE dbo.LoginHistory  
CREATE TABLE dbo.LoginHistory (  
Username VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,  
LoginTime DATETIME NOT NULL,  
ProgramName VARCHAR(255)  
)  
END  
IF (OBJECT_ID('dbo.LastLogin') IS NULL)  
BEGIN  
-- DROP TABLE dbo.LastLogin  
CREATE TABLE dbo.LastLogin (  
Username VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,  
CreateDate DATETIME,  
LastLogin DATETIME NULL,  
DaysSinceLastLogin AS (DATEDIFF(DAY, ISNULL(LastLogin, CreateDate), CONVERT(DATE, GETDATE())))  
)  
END  

Creation of Trigger

   IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgAudit_LoginHistory') > 0) DROP TRIGGER [trgAudit_LoginHistory] ON ALL SERVER  
    GO  
    CREATE TRIGGER [trgAudit_LoginHistory] ON ALL SERVER  
    -- Para evitar problemas de permissão no insert na tabela  
    WITH EXECUTE AS 'sa'  
    FOR LOGON   
    AS  
    BEGIN  
    SET NOCOUNT ON  
    -- Não loga conexões de usuários de sistema  
    IF (ORIGINAL_LOGIN() LIKE 'NT %' OR ORIGINAL_LOGIN() LIKE '##%' OR ORIGINAL_LOGIN() LIKE '%SQLServerAgent')  
    RETURN  
    -- Não loga conexões de softwares que ficam se conectando constantemente  
    IF (PROGRAM_NAME() LIKE 'Red Gate%' OR PROGRAM_NAME() LIKE '%IntelliSense%' OR PROGRAM_NAME() LIKE 'SQLAgent %' OR PROGRAM_NAME() IN ('Microsoft SQL Server', 'RSPowerBI', 'RSManagement', 'TransactionManager', 'DWDiagnostics', 'Report Server'))  
    RETURN  
    INSERT INTO dbo.LoginHistory (Username, LoginTime, ProgramName)  
    SELECT  
    ORIGINAL_LOGIN(),  
    GETDATE(),  
    PROGRAM_NAME()  
    END  
    GO  
    ENABLE TRIGGER [trgAudit_LoginHistory] ON ALL SERVER    
    GO  

Generates the analysis of each user's last login

IF (OBJECT_ID('tempdb..#UltimoLogin') IS NOT NULL) DROP TABLE #UltimoLogin  
CREATE TABLE #UltimoLogin (  
[User] VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,  
LogDate DATETIME NOT NULL  
)  
INSERT INTO #UltimoLogin  
SELECT   
Username,   
MAX(LoginTime) AS LogDate  
FROM  
dbo.LoginHistory  
GROUP BY  
Username  
-- Insere os logins criados na instância  
INSERT INTO dbo.LastLogin (Username, CreateDate)  
SELECT  
[name],  
create_date  
FROM  
sys.server_principals A  
LEFT JOIN dbo.LastLogin B ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = B.Username  
WHERE  
is_fixed_role = 0  
AND [name] NOT LIKE 'NT %'  
AND [name] NOT LIKE '##%'  
AND B.Username IS NULL  
AND A.[type] IN ('S', 'U')  
-- Atualiza a tabela de histórico com os dados atuais  
UPDATE A  
SET  
A.LastLogin = B.LogDate  
FROM  
dbo.LastLogin A  
JOIN #UltimoLogin B ON A.Username = B.[User]  
WHERE  
ISNULL(A.LastLogin, '1900-01-01') <> B.LogDate  
SELECT *   
FROM dbo.LastLogin  

• Using Server Audit

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.