Audit Users logging in using "sa" or service accounts

Tags: SQL Server, audit, login, logon, xml, email, mail
Thursday, July 4, 2019 11:58:47 PM
source: https://www.sqlservercentral.com/scripts/audit-users-logging-in-using-sa-or-service-accounts


IF OBJECT_ID ('AuditSqlLogins') IS NOT NULL DROP TABLE AuditSqlLogins 
GO

CREATE TABLE AuditSqlLogins (
	[Counter] [int] IDENTITY(1,1) NOT NULL,
	[Login_name] [varchar](20) NULL,
	[host_name] [varchar](20) NULL,
	[login_time] [datetime] NULL
) ON [PRIMARY]

INSERT INTO [dbo].[AuditSqlLogins]
	(login_time, login_name, host_name )
   
	SELECT login_time, login_name, host_name 
	FROM sys.dm_exec_sessions 
	WHERE host_name not like '%APP%'  -- excludeds Apllication server logins
	and host_name not like '%WWW%'    -- excludeds Web server logins
	and database_id = 5               -- Specify the database you want to audit
	and login_name = 'Appadmin'       -- specify the account you want audited
	or login_name = 'sa'
	ORDER BY host_name desc

	DELETE FROM AuditSqlLogins
	WHERE host_name is null

DECLARE @counter integer
SELECT @counter = Counter FROM AuditSqlLogins                         

IF (@counter > 0)
BEGIN 

DECLARE @xml NVARCHAR(MAX)

SET @xml = CAST ( ( SELECT 
		  td = login_name  , '',
		  td = host_name, '',
          td = login_time,''

FROM AuditSqlLogins 
 
FOR XML PATH('tr'), TYPE 
              ) AS NVARCHAR(MAX) )
       DECLARE @body  nvarchar(max);
       SET @body ='
<html>
<body>
<head>
<style>
table {
  border-collapse: collapse;
}
table, th, td {
  border: 1px solid #ea5685;
}
</style>
</head>
<H3>Audit Apadmin and SA Login Event : (Server Name)</H3>  
            
<table border="1" cellspacing="0" border-spacing="0" style ="text-align:center"> 
<tr style="height:15px; background:#ea8686">
<th border-spacing="0" style="padding:5px 15px;">login_name</th>
<th border-spacing="0" style="padding:5px 15px;">host_name</th>
<th border-spacing="0" style="padding:5px 15px;">login_time</th>
</tr>
'     
SET @body = @body+@xml+'</table><body>'

EXEC msdb.dbo.sp_send_dbmail
@recipients='[email protected]', 
       @subject = 'Audit itsadmin and SA Login Event',
       @body = @body,
       @body_format = 'HTML' ,
       @profile_name='Your DB Mail profile'        
END
DROP TABLE AuditSqlLogins
No Comments

You must log on to comment.