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”

Create table to log on [Master] database.

USE [master]
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

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)
    CONVERT(char(10), GETDATE(), 126) AS ConnectionDate,
    RTRIM(loginame) AS loginame,
      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 :




Les commentaires sont fermés.