SQLDBA- Get logging activity into sql server

-

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



Comments

Post a Comment

Popular posts from this blog

DBA- MAXDOP

-
Image
  Have your SQL queries been eating up CPU like it's a buffet? Then it’s time to talk about the most underrated setting in SQL Server:  MAXDOP . Let’s break it down in simple, real-world terms — with best practices, optimized queries, and use cases that matter. What is MAXDOP? MAXDOP stands for Maximum Degree of Parallelism — it controls how many CPU cores SQL Server uses to run a single query in parallel. Sounds powerful? It is — but misconfigured, it can hurt more than help. Too low? Queries run slower, underutilizing hardware. Too high? CPU gets overwhelmed, blocking other tasks. 🔧  Where to Configure MAXDOP : Instance Level (Recommended for Most) EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'max degree of parallelism', 4; RECONFIGURE; Database Level (Overrides Instance Setting) ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2; Query Level (...
Read more

DBA - Check health and status of Always On or Availability Group using DMVs

-
 -- Health and status of WSFC cluster. These two queries work only if the WSFC has quorum SELECT * FROM sys.dm_hadr_cluster SELECT * FROM sys.dm_hadr_cluster_members -- Health of the AGs SELECT ag.name agname, ags.* FROM sys.dm_hadr_availability_group_states ags  INNER JOIN sys.availability_groups ag ON ag.group_id = ags.group_id -- Health and status of AG replics from the WsFC perspective SELECT ar.replica_server_name,harc.* FROM sys.dm_hadr_availability_replica_cluster_states harc  INNER JOIN sys.availability_replicas ar ON ar.replica_id = harc.replica_id -- Health and status of AG replicas, run this on the primary replica.  -- On secondary this will only show info for that instance SELECT * FROM sys.dm_hadr_availability_replica_states  -- Health and status of AG databases from the WSFC perspective SELECT * FROM sys.dm_hadr_database_replica_cluster_states  -- Health and status of AG databases, run this on the primary replica.  -- On secondary this wi...
Read more

DBA - Script out the Linked server objects.

-
  Script out the Linked server objects.        CREATE  PROCEDURE [dbo].[win_linked_server_objects_extract_select]     AS       /***********************************************************************************************************    --Purporse: To extract Linkded server connection details      --Created By: kovvuru subbareddy        Purpose:    TEST:        EXEC [dbo].[win_linked_server_objects_extract_select]       ***********************************************************************************************************/     BEGIN   SET NOCOUNT ON      CREATE TABLE #temp_linkedserver_detail   ( ID INTEGER identity(1,1) primary key NOT NULL,   create_linked_server_command VARCHAR(5000),   linked_server_name varchar(100) ...
Read more