Simple log connections SQLServer 2000
Rédigé par Sozezzo - - Aucun commentaireThis code create a table to log all connections.
It is very simple, it just logs when, who, from where and which database.
For this we use “master.dbo.sysprocesses”
Create table to log on [Master] database.
USE [master] GO CREATE TABLE [dbo].[dbaLogAccess] ( [dbaLogAccessId] [int] IDENTITY (1, 1) NOT NULL , [ConnectionDate] [datetime] NULL , [loginame] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL , [hostname] [nchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL , [dbname] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ) ON [PRIMARY]
Script to add logs, you can run each minute to update the log. This script avoids to repeater, it only saves new connections. We recommend using a job and schedule each minute to run this script.
INSERT INTO master.dbo.dbaLogAccess (ConnectionDate, loginame, hostname, dbname) SELECT DISTINCT tbNew.ConnectionDate, tbNew.loginame, tbNew.hostname, tbNew.dbname FROM (SELECT CONVERT(char(10), GETDATE(), 126) AS ConnectionDate, RTRIM(loginame) AS loginame, hostname, CASE WHEN dbid = 0 THEN '' WHEN dbid <> 0 THEN DB_NAME(dbid) END AS dbname FROM master.dbo.sysprocesses WHERE (hostname <> '')) AS tbNew LEFT OUTER JOIN master.dbo.dbaLogAccess AS tbOld ON tbNew.ConnectionDate = tbOld.ConnectionDate AND tbNew.loginame = tbOld.loginame AND tbNew.hostname = tbOld.hostname AND tbNew.dbname = tbOld.dbname WHERE (tbOld.dbaLogAccessId IS NULL);
This code limit connection by day, we can change by hour or by minute, you just change how many character you use it:
print CONVERT(char(14), GETDATE(), 126) + '00:00' print CONVERT(char(17), GETDATE(), 126) + '00' print CONVERT(char(20), GETDATE(), 126)
Well, you may do not like to use [master] database, but it is the place to record the system-level information. Good news, you can easily change it.
or you can use : https://www.sqlshack.com/creating-successful-auditing-strategy-sql-server-databases/