Audit SA logins in SQL servers

Question

We want to capture the SA login to the SQL instance. We have deployed a logon trigger to record SA logins in a table and it is working fine. For a few SQL instances, the application is losing connectivity to the database even if the application is not using SA login to connect the database.

CREATE TRIGGER [trgAudit_LoginHistory] ON ALL SERVER  
WITH EXECUTE AS 'sa'  
FOR LOGON   
AS  
BEGIN  
  
  
    SET NOCOUNT ON  
      
    
  
IF (ORIGINAL_LOGIN() = 'sa') and @@spid >=50 --avoid system connections--SUSER_SNAME()  
BEGIN  
    INSERT INTO _dbMaintenance.maint.LoginHistory --(Username, LoginTime, ProgramName)  
    SELECT distinct top 1  
        ORIGINAL_LOGIN() username,  
        GETDATE() logintime,  
        PROGRAM_NAME() ProgramName  
,HOST_NAME()  
 FROM sys.dm_exec_connections  
 WHERE session_id = @@spid  
 and @@spid >=50 --avoid system connections',  
  
END  

Answer 1

Hi,

(1) Your query is missing a closure of END. You open twice using BEGIN and only close one of these.

(2) I agree with @Prasad Chava , please clarify the question.

(3) You should use the explicit name of the columns when you INSERT the data!

(4) What did you meant when you used "SELECT distinct top 1" ?!?

If you select TOP 1 then why do you need distinct ? How a single row can be not distinct from itself?

Note!!! When you are using SSMS and start connection to the server then the application create multiple connections and you might get more than one row since the trigger will be executed multiple times (once for each connection). This distinct or top 1 is not relevant in that case since it is not done in one execution but the trigger itself run multiple times.

I recommend to add another column for the session ID.

(5) Why do you need to filter in the query "and @@spid >=50" when this is already filtered in the IF condition "IF (ORIGINAL_LOGIN() = 'sa') and @@spid >=50"? If the @@spid is not >=50 then the INSERT query will not be executed anyway and vise versa.

(6) I recommend NOT to use "EXECUTE AS N'sa'". If needed then CREATE a LOGIN and USER for the task

Back to the issue

Note that your trigger does not seems to have any content, which directly lead to disconnecting the user, unless there is an error in the INSERT query itself. Since the trigger is executed as part of the login transaction, an error might be the reason to what you describe. You can try to monitor the errors using Extended Events.

Here is a fully working Demo

---------------------------------------------  
---------------------------------------------  
USE master  
GO  
CREATE DATABASE _dbMaintenance  
GO  
USE _dbMaintenance  
GO  
CREATE SCHEMA maint  
GO  
DROP TABLE IF EXISTS maint.LoginHistory  
GO  
CREATE TABLE maint.LoginHistory(  
 Username NVARCHAR(MAX),   
 LoginTime DATETIME,  
 ProgramName NVARCHAR(MAX),  
 HostName NVARCHAR(MAX),  
 SessionID INT  
)  
GO  
  
SELECT * FROM maint.LoginHistory  
GO  
  
---------------------------------------------  
---------------------------------------------  
-- Logon Triggers  
-- https://learn.microsoft.com/sql/relational-databases/triggers/logon-triggers?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699  
  
USE master;    
GO  
  
CREATE OR ALTER TRIGGER [trgAudit_LoginHistory] ON ALL SERVER WITH EXECUTE AS N'sa' FOR LOGON AS BEGIN  
  
 SET NOCOUNT ON  
  
 --SELECT ORIGINAL_LOGIN() -- ATOR31\ronen  
  
 IF (ORIGINAL_LOGIN() = 'sa') and @@spid >=50 --avoid system connections--SUSER_SNAME()  
 BEGIN  
 INSERT INTO _dbMaintenance.maint.LoginHistory (Username, LoginTime, ProgramName, HostName, sessionID)  
 SELECT DISTINCT top 1  
 ORIGINAL_LOGIN() username,  
 GETDATE() logintime,  
 PROGRAM_NAME() ProgramName,  
 HOST_NAME(),  
 @@SPID  
 FROM sys.dm_exec_connections  
 WHERE session_id = @@spid and @@spid >=50 --avoid system connections',  
      
 END  
END  
GO  
  
  
---------------------------------------------  
---------------------------------------------  
-- Clean  
USE master  
GO  
DROP TRIGGER IF EXISTS [trgAudit_LoginHistory] ON ALL SERVER  
GO  
DROP DATABASE IF EXISTS _dbMaintenance  
GO