A while ago the manager asked me if I could record the time that the user last logged into our business database because someone had modified the login password for the database SA user before, so we need to record it.
I checked the data, as if I could not record who logged in to the business library, can only record who logged on to SQL Server
Database version is SQL2005, operating system: WINDOWS7
Here is a script I wrote, my implementation is the use of triggers, triggers are login triggers, the scope of the entire server scope, if someone has logged in, use the bcp command to log the login information logging files
1, if the original database already exists trigger delete him
Copy Code code as follows:
Use MASTER
Go
DROP TRIGGER trg_logon_attempttest on all SERVER
Go
2. Create a new text file in D disk d:\Logondata.txt This text file is used to record login information
3, create a login trigger Audit logon events
Copy Code code as follows:
CREATE TRIGGER Trg_logon_attempttest
On the 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), GETDATE (), 121)
+ ' >> d:\Logondata.txt '
DECLARE @tb_re TABLE (re varchar (4000));
INSERT @tb_re exec master. xp_cmdshell @cmd
End
Go
This will log logon hours and logon username every time you log on to SQL Server
Before creating a trigger, you need to turn on the xp_cmdshell extended stored procedure and do not disable the SA user
Otherwise, you will not be able to log on to the server, my Computer name is Joe
If this is the case, you can use SQL Server's dedicated Administrator connection (DAC) to connect to the server and remove the trigger first
The style of the log is this:
Copy Code code 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
Now there are two unresolved issues:
(1) I just want to record the non-Windows authentication mode of the user login, do not want to record the Windows authentication method, now has not found a way
(2) Modify the password of the login user to record the action, but looked for a long time did not find what function to use
Perhaps this article also has the mistake, welcome everybody to shoot the Brick O (∩_∩) o!!
