SQL statement used to log on to a user

SQLSERVER records the logon time of a logon user. 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, just use

SQLSERVER records the logon time of a logon user. 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, just use

The bcp Command records the logon information in the log file.

1. If a trigger already exists in the original database, delete it.

The Code is as follows:
1 USE MASTER 2 GO 3 drop trigger trg_logon_attempttest ON ALL SERVER 4 GO2

Create a text file d: Logondata.txt on disk d to record logon information.

3. Create a logon trigger to review logon events

The 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.

Share

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:

The Code is as follows:

1 nt authoritysystem 16:49:04. 140 2 nt authoritysystem 16:49:14. 210 3 nt authoritysystem 2013-02-08 16:49:24. 277 4 JOEAdministrator 2013-02-08 16:49:31. 753 5 JOEAdministrator 2013-02-08 16:49:31. 963 6 nt authoritysystem 16:49:34. 327 7 JOEAdministrator 2013-02-08 16:49:35. 777 8 sa 16:51:39. 930 9 nt authoritysystem 2013-02-08 16:52:03. 147 10 nt authoritysystem 16:52:13. 337 11 nt authoritysystem 16:52:23. 410 12 nt authoritysystem 2013-02-08 16:52:33. 830 13 nt authoritysystem 2013-02-08 16:52:44. 703 14 nt authoritysystem 2013-02-08 16:52:54. 407 15 nt authoritysystem 2013-02-08 16:52:54. 623 16 nt authoritysystem 2013-02-08 16:52:54. 797 17 nt authoritysystem 2013-02-08 16:52:54. 823 18 nt authoritysystem 2013-02-08 16:52:54. 893 19 nt authoritysystem 2013-02-08 16:52:55. 147 20 nt authoritysystem 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.