To know who is connected to the SQL server . we need to schedule this in SQL agent to run very minimal time to capture all the login information.
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[chi_logon](
[LoginName] [nvarchar](100) NOT NULL,
[ClientHost] [nvarchar](100) NOT NULL,
[LoginType] [nvarchar](100) NULL,
[LastPostTime] [datetime] NOT NULL,
[ServerName] [nvarchar](100) NOT NULL,
CONSTRAINT [pk_chi_logon] PRIMARY KEY CLUSTERED
(
[LoginName] ASC,
[ClientHost] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[chi_logon_log](
[PostTime] [datetime] NULL,
[ServerName] [nvarchar](100) NULL,
[LoginName] [nvarchar](100) NULL,
[LoginType] [nvarchar](100) NULL,
[ClientHost] [nvarchar](100) NULL
) ON [PRIMARY]
GO
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Administrative' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Administrative'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ADM - Get Logon Activity',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Administrative',
@owner_login_name=N'sa',
@notify_email_operator_name=N'DBA ALERT', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Update known connections] Script Date: 2/23/2026 9:25:36 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update known connections',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'--Update REcords
WITH logon_CTE (logon, client_address, auth_scheme)
AS
(
SELECT DISTINCT s.[loginame] AS [loginame],
e.[client_net_address] AS [client_address],
e.[auth_scheme]
from sys.dm_exec_connections as e with (NOLOCK)
left outer join sys.sysprocesses as s with (NOLOCK)
on s.[spid] = e.session_id
WHERE s.loginame IS NOT NULL
)
UPDATE l
SET [LoginType] = c.[auth_scheme]
,[LastPostTime] = GETDATE()
FROM dba.dbo.[chi_logon] as l
INNER JOIN logon_CTE as c
ON c.logon = l.LoginName
AND c.client_address = l.ClientHost
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Insert new connections] Script Date: 2/23/2026 9:25:36 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert new connections',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'--Insert new records
INSERT INTO dba.dbo.[chi_logon]
([LoginName]
,[ClientHost]
,[LoginType]
,[LastPostTime]
,[ServerName])
SELECT DISTINCT s.[loginame] AS [loginame],
e.[client_net_address] AS [client_address],
e.[auth_scheme],
GETDATE(),
@@SERVERNAME
from sys.dm_exec_connections as e with (NOLOCK)
left outer join sys.sysprocesses as s with (NOLOCK)
on s.[spid] = e.session_id
WHERE s.loginame IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM dba.dbo.chi_logon as c
WHERE c.LoginName = s.loginame
AND c.ClientHost = e.client_net_address)
ORDER BY [loginame]
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'1',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=5,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20130607,
@active_end_date=99991231,
@active_start_time=300,
@active_end_time=235959,
@schedule_uid=NULL;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
No comments:
Post a Comment