简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
Home > Database > Mysql Tutorial > body text

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

WBOY
Release： 2016-06-07 15:44:55
Original
1684 people have browsed it

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>
Copy after login

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>
Copy after login

这样当每次登录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>
Copy after login

 

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

（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>
Copy after login

文章出处:

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

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

 

 

Related labels：
sqlserver time user Log in Record
source：php.cn
Previous article：SQLSERVER中的自旋锁 Next article：oracle 数据类型详解
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
SQL Server: Using CASE WHEN expression with WHERE clause I have a new SpringBoot project with SQLServer and I need to replace the MySQL native quer...
From 2024-04-03 14:46:55
0
1
351
Unable to connect to SQL Server in Laravel I can't connect to sql-server in Laravel. I have given sqlserver configuration values ​​in...
From 2024-03-31 13:08:48
0
1
395
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn：Public welfare online PHP training，Help PHP learners grow quickly！