Simple log connections SQLServer 2000

Rédigé par M Sozezzo - - Aucun commentaire

This 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”

 

https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysprocesses-transact-sql?view=sql-server-2017


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/

 

 

 

Fil RSS des articles