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.
1USE [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.
1 INSERT INTO master.dbo.dbaLogAccess (ConnectionDate, loginame, hostname, dbname) SELECT DISTINCT tbNew.ConnectionDate, tbNew.loginame, tbNew.hostname, tbNew.dbname
2 FROM (
3
4 SELECT
5 CONVERT(char(10),
6 GETDATE(), 126) AS ConnectionDate,
7 RTRIM(loginame) AS loginame,
8 hostname,
9 CASE WHEN dbid = 0 THEN '' WHEN dbid <> 0 THEN DB_NAME(dbid) END AS dbname
10 FROM master.dbo.sysprocesses
11 WHERE (hostname <> '')
12
13 ) AS tbNew LEFT OUTER JOIN master.dbo.dbaLogAccess AS tbOld
14 ON tbNew.ConnectionDate = tbOld.ConnectionDate AND tbNew.loginame = tbOld.loginame AND tbNew.hostname = tbOld.hostname AND tbNew.dbname = tbOld.dbname
15 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:
1print CONVERT(char(14), GETDATE(), 126) + '00:00'
2print CONVERT(char(17), GETDATE(), 126) + '00'
3print 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/
Comments