简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
首頁 > 資料庫 > mysql教程 > 主體

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

WBOY
發布： 2016-06-07 15:44:55
原創
1636 人瀏覽過

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记录登录用户的登录时间

 

如果遇到这种情况可以使用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数据库所做的操作，非常详细，可惜由于他使用服务器范围的触发器

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

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

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

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

查了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

 

 

相關標籤：
sqlserver 時間 使用者 登入 記錄
來源：php.cn
上一篇：SQLSERVER中的自旋锁 下一篇：oracle 数据类型详解
本網站聲明
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn
作者最新文章
最新問題
XAMPP/SQLSRV：無法在 PHPINFO() 中找到 Sqlsrv; - 來自連線的錯誤 我正在嘗試連接到我在LinuxVM上託管的SQLServer資料庫。我在我的開發Windows機器上運行xampp，連接來自我正在建立的php網站。我想我需要使用sqlsrv來連線...
來自於 2023-11-09 22:34:33
0
1
172
MySQL - 將行轉換為列 我嘗試搜尋帖子，但只找到了SQLServer/Access的解決方案。我需要MySQL(5.X)中的解決方案。我有一個包含3列的表（稱為歷史記錄）：hostid、itemname、...
來自於 2023-10-13 11:50:31
0
1
232
為什麼 MySQL 中只有列名而沒有等於值的 where 不是語法錯誤 為什麼這樣的查詢不是語法錯誤？我有SQLServer背景，我真的很驚訝。從my_tableWHEREid中選擇*我認為它會驗證它是否具有值，但行為並不一致，使用id時它會返回具有i...
來自於 2023-09-16 10:37:20
0
1
295
嘗試在查詢限制後進行排序的實驗 所以這是我在這裡的第一個問題，如果有任何不對的地方，請原諒。我試圖按照限制條件進行排序查詢。所以有一個包含90筆記錄的Customers表。我想先進行限制為3筆記錄的查詢，然後再按...
來自於 2023-09-05 14:46:42
0
1
350
HTML 中的背景或字型顏色為紅色 我有以下程式碼可以向收件者發送電子郵件通知，效果很好。來源資料來自SQLServer查詢，我暫時呼叫「#Temp_Warning」表。如果T.FORMATTED_ENTRY的值小於...
來自於 2023-09-03 09:18:19
0
1
333
相關專題
更多>
熱門推薦
熱門教學
更多>
相關教學
熱門推薦
最新課程
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板
關於我們 免責聲明 Sitemap
PHP中文網：公益線上PHP培訓，幫助PHP學習者快速成長！