1 --实现白的有人登录过数据库或者改过密码都记录日志的需求 2013-2-6
2
3 --注意：如果你登录不了数据库，不管是sa还是其他登录用户
4 --这时候要使用DAC连接进去数据库，不用重装
5 --数据库，在SSMS左上角的新建查询那里连接数据库
6 --因为DAC连接只能使用左上角的新建查询，然后删除触发器就可以了
7 USE MASTER
8 GO
9 DROP TRIGGER trg_logon_attempttest ON ALL SERVER
10 GO
11
12 -----------------------------------------------------------------------------------------
13 --创建一个登录触发器审核登录事件
14 --------------------------------------------------------
15
16
17 --------/***************
18 --------演示数据库和审核表
19 --------3w@live.cn
20 --------*******************/
21
22 CREATE DATABASE ExampleAuditDB
23 GO
24 USE ExampleAuditDB
25 GO
26
27 CREATE TABLE dbo.RestrictedLogonAttempt
28 (LoginNM sysname NOT NULL,
29 AttemptDT DATETIME NOT NULL)
30 GO
31
32 --------/***************
33 --------创建登录触发器
34 --------3w@live.cn
35 --------*******************/
36
37 CREATE TRIGGER trg_logon_attempttest
38 ON ALL SERVER
39 WITH EXECUTE AS'sa'
40 FOR LOGON,ALTER_LOGIN
41 AS
42 BEGIN
43 --INSERT ExampleAuditDB.dbo.RestrictedLogonAttempt
44 --(LoginNM, AttemptDT)
45 --VALUES (ORIGINAL_LOGIN(), GETDATE())
46 DECLARE
47 @cmd nvarchar(4000)
48 ;
49 SELECT
50 @cmd = 'ECHO '
51 + ORIGINAL_LOGIN()+ CHAR(9) + CONVERT(varchar(100), GETDATE(), 121)
52 + ' >> d:\Logondata.txt'
53 ;
54 DECLARE @tb_re TABLE(re varchar(4000)); --如果使用表的话,这里的表变量改为你的数据库里的表即可,这里使用增量记录日志文件就可以了
55 INSERT @tb_re exec master.. xp_cmdshell @cmd
56 END
57 GO
58
59 --------/***************
60 --------查看审核记录
61 --------3w@live.cn
62 --------*******************/
63 USE ExampleAuditDB
64 GO
65 SELECT * from dbo.RestrictedLogonAttempt
66 go
67
68 --------/***************
69 --------删除演示数据库及演示触发器
70 --------3w@live.cn
71 --------*******************/
72 USE [ExampleAuditDB]
73 go
74
75 USE MASTER
76 GO
77 DROP TRIGGER trg_logon_attempttest ON ALL SERVER
78 GO
79
80 DROP database ExampleAuditDB
81 go
82
83 ------------------------------下篇------------------------------------------------
84
85 --http://msdn.microsoft.com/zh-cn/library/ms173781.aspx
86 --http://msdn.microsoft.com/zh-cn/library/ms186456(v=SQL.90).aspx
87 USE [pratice];
88 GO
89 CREATE TABLE ddl_log (PostTime datetime,DatabaseName NVARCHAR(100), DB_User nvarchar(100), Event nvarchar(100),LoginName NVARCHAR(100), TSQL nvarchar(2000));
90 GO
91
92 SELECT * FROM [dbo].[ddl_log]
93
94
95 ALTER TRIGGER tri_LogServerEvent
96 ON DATABASE --或者服务器级别 ALL SERVER
97 FOR DDL_DATABASE_LEVEL_EVENTS --或者服务器级别：DDL_SERVER_LEVEL_EVENTS
98 AS
99 DECLARE @data XML
100 SET @data = EVENTDATA()
101 INSERT ddl_log
102 (PostTime,DatabaseName, DB_User, Event, LoginName,TSQL)
103 VALUES
104 (GETDATE(),
105 @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),
106 CONVERT(nvarchar(100), CURRENT_USER),
107 @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
108 @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),
109 @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
110 GO
111 --Test the trigger.
112 CREATE TABLE TestTable (a int);
113 DROP TABLE TestTable ;
114 GO
115 SELECT * FROM ddl_log ;
116 GO
117 --Drop the trigger.
118 DROP TRIGGER tri_LogServerEvent
119 ON DATABASE;
120 GO
121 --Drop table ddl_log.
122 DROP TABLE ddl_log;
123 GO
124
125 --------------------------------------------------------------
126 CREATE TRIGGER tri_LogServerEvent
127 ON ALL SERVER --服务器级别
128 FOR DDL_SERVER_LEVEL_EVENTS --服务器级别
129 AS
130 DECLARE @data XML
131 SET @data = EVENTDATA()
132 INSERT ddl_log
133 (PostTime,DatabaseName, DB_User, Event, LoginName,TSQL)
134 VALUES
135 (GETDATE(),
136 @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),
137 CONVERT(nvarchar(100), CURRENT_USER),
138 @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
139 @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),
140 @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
141 GO
142
143 --这样写会报错
144 --消息 1082，级别 15，状态 1，过程 tri_LogServerEvent，第 15 行
145 --"DROP_SERVER_ROLE_MEMBER" 不支持同步触发器注册。
146 ----------------------------------------------------------------
147 CREATE TRIGGER tri_LogServerEvent
148 ON ALL SERVER --服务器级别
149 FOR DDL_DATABASE_LEVEL_EVENTS --数据库级别
150 AS
151 DECLARE @data XML
152 SET @data = EVENTDATA()
153 INSERT ddl_log
154 (PostTime,DatabaseName, DB_User, Event, LoginName,TSQL)
155 VALUES
156 (GETDATE(),
157 @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),
158 CONVERT(nvarchar(100), CURRENT_USER),
159 @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
160 @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),
161 @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
162 GO
163
164 --这样写会报错
165 --消息 1098，级别 15，状态 1，过程 tri_LogServerEvent，第 15 行
166 --指定的事件类型对指定的目标对象无效。
167 -------------------------------------------------------------------------
168 CREATE TRIGGER tri_LogServerEvent
169 ON DATABASE --数据库级别
170 FOR DDL_SERVER_LEVEL_EVENTS --服务器级别
171 AS
172 DECLARE @data XML
173 SET @data = EVENTDATA()
174 INSERT ddl_log
175 (PostTime,DatabaseName, DB_User, Event, LoginName,TSQL)
176 VALUES
177 (GETDATE(),
178 @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),
179 CONVERT(nvarchar(100), CURRENT_USER),
180 @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
181 @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),
182 @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
183 GO
184
185 --这样写会报错
186 --消息 1098，级别 15，状态 1，过程 tri_LogServerEvent，第 15 行
187 --指定的事件类型对指定的目标对象无效。
