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

SQLSERVER记录登录用户的登录时间 前一阵子经理问我能不能把用户最后一次登录我们的业务数据库的时间记录下来，因为之前有人修改过数据库sa用户 的登录密码，所以我们要记录一下。 我查了一下资料，好像不能记录谁登录过业务库，只能记录谁登录过SQLSERVER

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

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

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

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

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

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

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

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

<span>1</span> <span>USE</span><span> MASTER
</span><span>2</span> <span>GO</span>
<span>3</span> <span>DROP</span> <span>TRIGGER</span> trg_logon_attempttest <span>ON</span> <span>ALL</span><span> SERVER
</span><span>4</span> <span>GO</span>

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

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

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

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

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

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

 

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

 

日志的样式是这样的：

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

 

---

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

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

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

 

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

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

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

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

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

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

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

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

文章出处:

http://msdn.microsoft.com/zh-cn/library/ms173781.aspx

http://msdn.microsoft.com/zh-cn/library/ms186456(v=SQL.90).aspx