日期:2014-05-17  浏览次数:20495 次

sqlserver2005连接到远程服务器
我本地的sqlserver 通过IP端口号,账号 密码 连接服务器的sqlserver2005如图。

我想知道,服务器 如何记录这种连接。
谢谢大家



------解决方案--------------------
看不到图,不过视乎你想要的东西这么查
select * from sysprocesses
------解决方案--------------------
这是一个数据库层次的触发器,你了解一下个大概。是记录每个连接在该数据库上做的操作,并记录操作及连接的相关信息,我刚才那句就是在这里取出来的。
SQL code
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/16/2012 17:21:56 ******/
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/16/2012 17:21:56 ******/
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