在SQL Server中是否有一种方法可以查看所有连接的服务器/客户端的历史记录,包括它们对应的主机名、IP地址和上次连接日期?
基本上我想要的是sp_who2
存储过程的历史记录表。
默认情况下是否存在这样或类似的东西,以便我可以检索上述信息?
在SQL Server中是否有一种方法可以查看所有连接的服务器/客户端的历史记录,包括它们对应的主机名、IP地址和上次连接日期?
基本上我想要的是sp_who2
存储过程的历史记录表。
默认情况下是否存在这样或类似的东西,以便我可以检索上述信息?
如果您可以使用触发器,这里有一个解决方案。
如果运行此脚本,则每次登录时,该逻辑事件的详细信息将被记录到[master].[dbo].[TRACETABLE]中。
CREATE TABLE [master].[dbo].[TRACETABLE] (
[EVENTDATE] DATETIME NOT NULL,
[DBNAME] NVARCHAR(128) NULL,
[CURRENTUSER] NVARCHAR(128) NULL,
[HOSTNAME] NVARCHAR(128) NULL,
[APPLICATIONNAME] NVARCHAR(128) NULL,
[PROCEDURENAME] NVARCHAR(128) NULL,
[USERID] SMALLINT NULL,
[USERNAME] NVARCHAR(128) NULL,
[SUSERID] INT NULL,
[SUSERNAME] NVARCHAR(128) NULL,
[IS_SERVERADMIN_SYSADMIN] INT NULL,
[IS_DB_OWNER] INT NULL,
[IS_DDL_ADMIN] INT NULL,
[IS_DB_DATAREADER] INT NULL,
[ORIGINAL_LOGIN] NVARCHAR(4000) NULL,
[NET_TRANSPORT] SQL_VARIANT NULL,
[PROTOCOL_TYPE] SQL_VARIANT NULL,
[AUTH_SCHEME] SQL_VARIANT NULL,
[LOCAL_NET_ADDRESS] SQL_VARIANT NULL,
[LOCAL_TCP_PORT] SQL_VARIANT NULL,
[CLIENT_NET_ADDRESS] SQL_VARIANT NULL,
[PHYSICAL_NET_TRANSPORT] SQL_VARIANT NULL)
GO
GRANT INSERT ON [master].[dbo].[TRACETABLE] TO PUBLIC
GRANT SELECT on [master].[sys].[dm_exec_connections]
GO
CREATE TRIGGER Logon_Trigger_Track_IP
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO [master].[dbo].[TRACETABLE]
--the auditing snippet below works fine in a
--login trigger,
--database trigger
--or any stored procedure.
SELECT
getdate() AS EventDate,
DB_NAME() AS DBName,
CURRENT_USER AS CurrentUser,
HOST_NAME() AS HostName,
APP_NAME() AS ApplicationName,
OBJECT_NAME(@@PROCID) AS ProcedureName,
USER_ID() AS Userid,
USER_NAME() AS UserName,
SUSER_ID() AS sUserid,
SUSER_SNAME() AS sUserName,
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
ConnectionProperty('net_transport') AS 'net_transport',
ConnectionProperty('protocol_type') AS 'protocol_type',
ConnectionProperty('auth_scheme') AS 'auth_scheme',
ConnectionProperty('local_net_address') AS 'local_net_address',
ConnectionProperty('local_tcp_port') AS 'local_tcp_port',
ConnectionProperty('client_net_address') AS 'client_net_address',
ConnectionProperty('physical_net_transport') AS 'physical_net_transport'
END
GO
ENABLE TRIGGER [Logon_Trigger_Track_IP] ON ALL SERVER