Audit login time, login name, hostname ,program name, client Etc Connection tracking and other info

1.USE [master] GO /****** Object: Table [dbo].[DBALogonAudit] Script Date: 1/12/2023 4:39:31 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DBALogonAudit]( [SessionId] [int] NULL, [LogonTime] [datetime] NULL, [HostName] [varchar](50) NULL, [ProgramName] [varchar](500) NULL, [LoginName] [varchar](50) NULL, [ClientHost] [varchar](50) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO 2.---create trigger USE [master] GO /****** Object: DdlTrigger [LogonAuditTrigger] Script Date: 1/12/2023 4:26:12 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [LogonAuditTrigger] /* Creates trigger for logons */ ON ALL SERVER FOR LOGON AS BEGIN DECLARE @LogonTriggerData xml, @EventTime datetime, @LoginName varchar(50), @ClientHost varchar(50), @LoginType varchar(50), @HostName varchar(50), @AppName varchar(500) SET @LogonTriggerData = eventdata() SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)') SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)') SET @HostName = HOST_NAME() SET @AppName = APP_NAME() INSERT INTO DBALogonAudit SELECT @@spid, @EventTime, @HostName, @AppName, @LoginName, @ClientHost END GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ENABLE TRIGGER [LogonAuditTrigger] ON ALL SERVER GO 3. put below inside a sql job --table should exist in a database Use master go DECLARE @xml NVARCHAR(MAX) DECLARE @body NVARCHAR(MAX) SET @xml = CAST(( SELECT @@SERVERNAME AS 'td','', s.name + '.' + t.Name AS 'td','', part.rows AS 'td','', CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3)) AS 'td' FROM SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id AND idx.Index_id = part.Index_id INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id where s.name + '.' + t.Name Like '%DBALogonAudit%' GROUP BY t.Name, s.name, part.rows ORDER BY 2 DESC FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @body ='

Schemas Info, Please vist master.dbo.DBALogonAudit table to get more detailed Login Report

' SET @body = @body + @xml +'

Servername	Tablename	RowsModified	TotalSpaceinGB

' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLMAil', -- replace with your SQL Database Mail Profile @body = @body, @body_format ='HTML', @recipients = 'abc@abc.com', -- replace with your email address @subject = 'Audit report' ;