SQLSERVER records the logon time of the logged-on user (self-written script)

A while ago, the manager asked me if I could record the last time the user logged on to our business database, because someone had previously modified the logon password of the database sa user, so we should record it.

I checked the information. It seems that I cannot record who has logged on to the business database. I can only record who has logged on to SQLSERVER.
The database version is SQL2005, and the operating system is Windows 7.

The following is a script written by myself. My implementation principle is to use a trigger. The trigger is a logon trigger and the range is the entire server range. If someone has logged on, use the bcp command to record the log file of the logon information.
1. If a trigger already exists in the original database, delete it.
Copy codeThe Code is as follows:
USE MASTER
GO
Drop trigger trg_logon_attempttest ON ALL SERVER
GO

2. Create a text file d: \ Logondata.txt on drive d to record logon information.
3. Create a logon trigger to review logon events
Copy codeThe Code is as follows:
Create trigger trg_logon_attempttest
ON ALL SERVER
With execute as 'sa'
For logon, ALTER_LOGIN
AS
BEGIN
DECLARE
@ Cmd nvarchar (4000)

SELECT
@ Cmd = 'echo'
+ ORIGINAL_LOGIN () + CHAR (9) + CONVERT (varchar (100), GETDATE (), 121)
+ '> D: \ Logondata.txt'

DECLARE @ tb_re TABLE (re varchar (4000 ));
INSERT @ tb_re exec master .. xp_mongoshell @ cmd
END
GO

In this way, every time you log on to SQLSERVER, the logon time and user name are recorded.
Before creating a trigger, you must enable the xp_mongoshell extension stored procedure and do not disable sa users.
Otherwise, the following problem occurs: I cannot log on to the server. My computer name is joe.
 
In this case, you can use the dedicated administrator (DAC) of sqlserver to connect to the server and delete the trigger first.
The log style is as follows:
Copy codeThe Code is as follows:
Nt authority \ SYSTEM 2013-02-08 16:49:04. 140
Nt authority \ SYSTEM 2013-02-08 16:49:14. 210
Nt authority \ SYSTEM 2013-02-08 16:49:24. 277
JOE \ Administrator 2013-02-08 16:49:31. 753
JOE \ Administrator 2013-02-08 16:49:31. 963
Nt authority \ SYSTEM 2013-02-08 16:49:34. 327
JOE \ Administrator 2013-02-08 16:49:35. 777
Sa 2013-02-08 16:51:39. 930
Nt authority \ SYSTEM 2013-02-08 16:52:03. 147
Nt authority \ SYSTEM 2013-02-08 16:52:13. 337
Nt authority \ SYSTEM 2013-02-08 16:52:23. 410
Nt authority \ SYSTEM 2013-02-08 16:52:33. 830
Nt authority \ SYSTEM 2013-02-08 16:52:44. 703
Nt authority \ SYSTEM 2013-02-08 16:52:54. 407
Nt authority \ SYSTEM 2013-02-08 16:52:54. 623
Nt authority \ SYSTEM 2013-02-08 16:52:54. 797
Nt authority \ SYSTEM 2013-02-08 16:52:54. 823
Nt authority \ SYSTEM 2013-02-08 16:52:54. 893
Nt authority \ SYSTEM 2013-02-08 16:52:55. 147
Nt authority \ SYSTEM 2013-02-08 16:52:55. 277

There are still two problems to solve:
(1) I only want to log on to a non-Windows authentication user and do not want to record the Windows authentication method. I have not found a method yet.
(2) The change of the logon user password should be recorded, but it has not been found for a long time.
Maybe this article is still wrong. You are welcome to make a brick o !!