SQL Server Management Studio -〉 管理-〉sql server 日志。这里有些
好像还可以写入 windows的系统日志里,我找找看
看数据库日志 和windows日志
--- 在日志记录发现如下登录,这能说明什么问题? Date 2010-04-28 14:23:44 Log Windows NT (Security)Source Security Category Logon/Logoff Event 540 User NT AUTHORITY\ANONYMOUS LOGON Computer Server_Name_xxxMessage Successful Network Logon: User Name: Domain: Logon ID: (0x0,0x5E6CCA47) Logon Type: 3 Logon Process: NtLmSsp Authentication Package: NTLM Workstation Name: xxxx Logon GUID: - Caller User Name: - Caller Domain: - Caller Logon ID: - Caller Process ID: - Transited Services: - Source Network Address: 192.168.xx.xxx Source Port: 0
如果只是想知道登录者IP地址,那可以用LOGIN TRIGGER记录下来。CREATE TABLE LogInfo(login VARCHAR(20),IP VARCHAR(100)) GOCREATE TRIGGER LoginIP_Recorder ON ALL SERVER FOR LOGON AS BEGIN declare @ip nvarchar(max) declare @loginName nvarchar(max)
SELECT @ip=EVENTDATA().value ('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)') SELECT @loginName=EVENTDATA().value ('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') if @loginName='sa' --你想监控的登录名 BEGIN INSERT INTO LogInfo(Login,IP) VALUES(@loginName,@ip) End END GO
Garnett_KG,非常感谢,一会儿试试你的方法!!!
LogInfo, LoginIP_Recorder 是不是应该放在master里面?
Msg 1084, Level 15, State 1, Procedure LoginIP_Recorder, Line 3 'LOGON' is an invalid event type.
和windows日志
--- 在日志记录发现如下登录,这能说明什么问题?
Date 2010-04-28 14:23:44
Log Windows NT (Security)Source Security
Category Logon/Logoff
Event 540
User NT AUTHORITY\ANONYMOUS LOGON
Computer Server_Name_xxxMessage
Successful Network Logon: User Name: Domain: Logon ID: (0x0,0x5E6CCA47) Logon Type: 3 Logon Process: NtLmSsp Authentication Package: NTLM Workstation Name: xxxx Logon GUID: - Caller User Name: - Caller Domain: - Caller Logon ID: - Caller Process ID: - Transited Services: - Source Network Address: 192.168.xx.xxx Source Port: 0
如果只是想知道登录者IP地址,那可以用LOGIN TRIGGER记录下来。CREATE TABLE LogInfo(login VARCHAR(20),IP VARCHAR(100))
GOCREATE TRIGGER LoginIP_Recorder
ON ALL SERVER
FOR LOGON
AS
BEGIN
declare @ip nvarchar(max)
declare @loginName nvarchar(max)
SELECT @ip=EVENTDATA().value
('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)')
SELECT @loginName=EVENTDATA().value
('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)')
if @loginName='sa' --你想监控的登录名
BEGIN
INSERT INTO LogInfo(Login,IP)
VALUES(@loginName,@ip)
End
END
GO
LogInfo,
LoginIP_Recorder
是不是应该放在master里面?
'LOGON' is an invalid event type.
你的SQL2005 还没打最新的补丁!打SP3吧。
只是学习