SQLSERVER记录登录用户的登录时间 前一阵子经理问我能不能把用户最后一次登录我们的业务数据库的时间记录下来，因为之前有人修改过数据库sa用户 的登录密码，所以我们要记录一下。 我查了一下资料，好像不能记录谁登录过业务库，只能记录谁登录过SQLSERVER
SQLSERVER记录登录用户的登录时间
前一阵子经理问我能不能把用户最后一次登录我们的业务数据库的时间记录下来，因为之前有人修改过数据库sa用户
的登录密码，所以我们要记录一下。
我查了一下资料，好像不能记录谁登录过业务库，只能记录谁登录过SQLSERVER
下面是本人写的一个脚本，我的实现原理是使用触发器，触发器是登录触发器，范围是整个服务器范围，如果有人登录过，就使用
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
文章出处:
http://msdn.microsoft测试数据/zh-cn/library/ms173781.aspx
http://msdn.microsoft测试数据/zh-cn/library/ms186456(v=SQL.90).aspx
查看更多关于SQLSERVER记录登录用户的登录时间的详细内容...