- Run SQL Managements Studioand execute the following code in Transact-SQL:
CREATE DATABASE LogonAudit /* Creates db for storing audit data */
USE [LogonAudit]
CREATE TABLE LogonAuditing /* Creates table for logons inside db */
(
SessionId int,
LogonTime datetime,
HostName varchar(50),
ProgramName varchar(500),
LoginName varchar(50),
ClientHost varchar(50)
)
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 [LogonAudit].[dbo].[LogonAuditing]
(
SessionId,
LogonTime,
HostName,
ProgramName,
LoginName,
ClientHost
)
SELECT
@@spid,
@EventTime,
@HostName,
@AppName,
@LoginName,
@ClientHost
END
GO
- In order to see the collected information about logons in the SQL Management Studio run the following script:
SELECT *
FROM [LogonAudit].[dbo].[LogonAuditing]
You can save data to CSV format after that.
Report sample:

- Run Netwrix Auditor, Navigate to Reports → Choose “SQL Server” → “All SQL Server Logons” →Click “Subscribe”.
- Define recipient and filters, save subscription. You can receive it via e-mail or have it delivered to a specified shared folder according to the schedule you set.
Report sample:
