sql server2000怎么查看远程连接ip

Question

RT
查看当前连接可以使用sp_who这个命令
sp_who下有hostname这一列 显示的是计算机名那么请问 
通过什么设置或者使用什么命令能查看到当前连接sql server的计算机的ip地址?

Answer 1

打开sql管理里面的日志  

Answer 2

你参考一下，这个语句可以查出在数据库上操作的DDL语句，里面有捕获ip地址的。IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'DDLTriggertTrace')
 DISABLE TRIGGER [DDLTriggertTrace] ON DATABASE
 
 GO
 
 
 
 /****** Object:  DdlTrigger [DDLTriggertTrace]    Script Date: 10/29/2012 11:28:34 ******/
 IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'DDLTriggertTrace')DROP TRIGGER [DDLTriggertTrace] ON DATABASE
 GO
 
 
 /****** Object:  DdlTrigger [DDLTriggertTrace]    Script Date: 10/29/2012 11:28:34 ******/
 SET ANSI_NULLS ON
 GO
 
 SET QUOTED_IDENTIFIER ON
 GO
 
 CREATE TRIGGER [DDLTriggertTrace] ON DATABASE
 --捕获存储过程、视图、表的创建、修改、删除动作
     FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_VIEW,
         ALTER_VIEW, DROP_VIEW, CREATE_TABLE, ALTER_TABLE, DROP_TABLE
 AS
     BEGIN
         SET NOCOUNT ON ;
         DECLARE @EventData XML = EVENTDATA() ;--返回有关服务器或数据库事件的信息，以XML格式保存。
         DECLARE @ip VARCHAR(32) = ( SELECT  client_net_address
                                     FROM    sys.dm_exec_connections
                                     WHERE   session_id = @@SPID
                                   ) ;
 
         INSERT  AuditDB.dbo.DDLEvents
                 ( EventType ,
                   EventDDL ,
                   EventXML ,
                   DatabaseName ,
                   SchemaName ,
                   ObjectName ,
                   HostName ,
                   IPAddress ,
                   ProgramName ,
                   LoginName
                 )
                 SELECT  @EventData.value('(/EVENT_INSTANCE/EventType)[1]',
                                          'NVARCHAR(100)') ,
                         @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
                                          'NVARCHAR(MAX)') ,
                         @EventData ,
                         DB_NAME() ,
                         @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',
                                          'NVARCHAR(255)') ,
                         @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',
                                          'NVARCHAR(255)') ,
                         HOST_NAME() ,
                         @ip ,
                         PROGRAM_NAME() ,
                         SUSER_SNAME() ;
     END
 
 GO
 
 SET ANSI_NULLS OFF
 GO
 
 SET QUOTED_IDENTIFIER OFF
 GO
 
 --DISABLE TRIGGER [DDLTriggertTrace] ON DATABASE
 GO
 
 
 

Answer 3

http://blog.csdn.net/wangjianming45/article/details/5600113