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.

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/