SQLSERVER记录登录用户的登录时间

前一阵子经理问我能不能把用户最后一次登录我们的业务数据库的时间记录下来，因为之前有人修改过数据库sa用户

的登录密码，所以我们要记录一下。

我查了一下资料，好像不能记录谁登录过业务库，只能记录谁登录过SQLSERVER

数据库版本是SQL2005 ，操作系统：Windows7

下面是本人写的一个脚本，我的实现原理是使用触发器，触发器是登录触发器，范围是整个服务器范围，如果有人登录过，就使用

bcp命令把登录信息记录日志文件

1、如果原来数据库已经存在触发器把他删掉

1 USE MASTER
2 GO
3 DROP TRIGGER trg_logon_attempttest ON ALL SERVER
4 GO

2、在D盘新建一个文本文件 d:\Logondata.txt 这个文本文件用来记录登录信息

3、创建一个登录触发器审核登录事件

 1 CREATE TRIGGER trg_logon_attempttest
 2 ON ALL SERVER
 3 WITH EXECUTE AS'sa'  
 4 FOR LOGON,ALTER_LOGIN
 5 AS
 6 BEGIN
 7 DECLARE
 8     @cmd nvarchar(4000)
 9 ;
10 SELECT
11     @cmd = 'ECHO '
12         + ORIGINAL_LOGIN()+ CHAR(9) + CONVERT(varchar(100), GETDATE(), 121)
13         + ' >> d:\Logondata.txt'
14 ;
15 DECLARE @tb_re TABLE(re varchar(4000));  
16 INSERT @tb_re exec master.. xp_cmdshell @cmd
17 END
18 GO

这样当每次登录SQLSERVER的时候就会记录登录时间和登录用户名

在创建触发器前，需要开启xp_cmdshell扩展存储过程，并且不要禁用sa用户

不然会遇到下面这种情况，登录不了服务器，我的计算机名是joe

如果遇到这种情况可以使用SQLSERVER的专用管理员连接（DAC）连接进服务器，并把触发器先删除掉

日志的样式是这样的：

 1 NT AUTHORITY\SYSTEM    2013-02-08 16:49:04.140 
 2 NT AUTHORITY\SYSTEM    2013-02-08 16:49:14.210 
 3 NT AUTHORITY\SYSTEM    2013-02-08 16:49:24.277 
 4 JOE\Administrator    2013-02-08 16:49:31.753 
 5 JOE\Administrator    2013-02-08 16:49:31.963 
 6 NT AUTHORITY\SYSTEM    2013-02-08 16:49:34.327 
 7 JOE\Administrator    2013-02-08 16:49:35.777 
 8 sa    2013-02-08 16:51:39.930 
 9 NT AUTHORITY\SYSTEM    2013-02-08 16:52:03.147 
10 NT AUTHORITY\SYSTEM    2013-02-08 16:52:13.337 
11 NT AUTHORITY\SYSTEM    2013-02-08 16:52:23.410 
12 NT AUTHORITY\SYSTEM    2013-02-08 16:52:33.830 
13 NT AUTHORITY\SYSTEM    2013-02-08 16:52:44.703 
14 NT AUTHORITY\SYSTEM    2013-02-08 16:52:54.407 
15 NT AUTHORITY\SYSTEM    2013-02-08 16:52:54.623 
16 NT AUTHORITY\SYSTEM    2013-02-08 16:52:54.797 
17 NT AUTHORITY\SYSTEM    2013-02-08 16:52:54.823 
18 NT AUTHORITY\SYSTEM    2013-02-08 16:52:54.893 
19 NT AUTHORITY\SYSTEM    2013-02-08 16:52:55.147 
20 NT AUTHORITY\SYSTEM    2013-02-08 16:52:55.277

现在还有两个问题没有解决：

（1）我只想记录非Windows验证方式的用户登录，不想记录Windows验证方式的，现在还没有找到方法

（2）修改登录用户密码的动作要记录，但是找了很久也没有找到使用什么函数

可能这篇文章还有错误，欢迎大家拍砖o(∩_∩)o ！！

昨天看了一下AdventureWorks数据库，原来他里面有一个表“dbo.DatabaseLog”

记录了人们对AdventureWorks数据库所做的操作，非常详细，可惜由于他使用服务器范围的触发器

来记录操作信息，而且这个触发器不是 “随库附送”，不然我也可以参考他的触发器代码自己做一个

他记录的内容真的非常详细

查了MSDN也找不到这个触发器，希望见过这个触发器或者知道这个触发器告知我一声，谢谢大家了 o(∩_∩)o

今晚在MSDN上找到监控代码,但是依然不能监控密码更改,还有触发器是数据库级别的,不能建立在服务器级别

 1 USE [pratice];
 2 GO
 3 CREATE TABLE ddl_log (PostTime datetime,DatabaseName NVARCHAR(100), DB_User nvarchar(100), Event nvarchar(100),LoginName NVARCHAR(100), TSQL nvarchar(2000));
 4 GO
 5 
 6 SELECT * FROM [dbo].[ddl_log]
 7 
 8 
 9 CREATE  TRIGGER tri_LogServerEvent 
10 ON DATABASE  --或者服务器级别 ALL SERVER 
11 FOR DDL_DATABASE_LEVEL_EVENTS  --或者服务器级别：DDL_SERVER_LEVEL_EVENTS
12 AS
13 DECLARE @data XML
14 SET @data = EVENTDATA()
15 INSERT ddl_log 
16    (PostTime,DatabaseName, DB_User, Event, LoginName,TSQL) 
17    VALUES 
18    (GETDATE(), 
19    @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),
20    CONVERT(nvarchar(100), CURRENT_USER), 
21    @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
22    @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'), 
23    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
24 GO
25 --Test the trigger.
26 CREATE TABLE TestTable (a int);
27 DROP TABLE TestTable ;
28 GO
29 SELECT * FROM ddl_log ;
30 GO
31 --Drop the trigger.
32 DROP TRIGGER tri_LogServerEvent
33 ON DATABASE;
34 GO
35 --Drop table ddl_log.
36 DROP TABLE ddl_log;
37 GO