use Management
go
CREATE TABLE dbo.ClientLoginInfo(
session_id int NULL,
most_recent_session_id int NULL,
connect_time datetime NOT NULL,
net_transport nvarchar(40) NOT NULL,
protocol_type nvarchar(40) NULL,
protocol_version int NULL,
endpoint_id int NULL,
encrypt_option nvarchar(40) NOT NULL,
auth_scheme nvarchar(40) NOT NULL,
node_affinity smallint NOT NULL,
num_reads int NULL,
num_writes int NULL,
last_read datetime NULL,
last_write datetime NULL,
net_packet_size int NULL,
client_net_address varchar(48) NULL,
client_tcp_port int NULL,
local_net_address varchar(48) NULL,
local_tcp_port int NULL,
connection_id uniqueidentifier NOT NULL,
parent_connection_id uniqueidentifier NULL,
most_recent_sql_handle varbinary(64) NULL,
login_id nvarchar(255) NULL
);
GO
create index nix_connection_id
on dbo.ClientLoginInfo(connection_id)
go
ALTER TRIGGER Client_Connection_Trigger
ON ALL SERVER WITH EXECUTE AS 'VIEW SERVER STATE 蟠 覈'
--願碓 覈詩覃 襦蠏語 蟾 譟一..
FOR LOGON
AS
BEGIN
IF NOT EXISTS (
SELECT TOP 1 1
FROM Management.dbo.ClientLoginInfo A --蠍 DB覈 覦蠖
INNER JOIN sys.dm_exec_connections B
ON A.connection_id = B.connection_id
WHERE B.session_id = @@SPID)
BEGIN
INSERT INTO Management.dbo.ClientLoginInfo --蠍 DB覈 覦蠖
(
session_id
, most_recent_session_id
, connect_time
, net_transport
, protocol_type
, protocol_version
, endpoint_id
, encrypt_option
, auth_scheme
, node_affinity
, num_reads
, num_writes
, last_read
, last_write
, net_packet_size
, client_net_address
, client_tcp_port
, local_net_address
, local_tcp_port
, connection_id
, parent_connection_id
, most_recent_sql_handle
, login_id
)
SELECT
session_id
, most_recent_session_id
, connect_time
, net_transport
, protocol_type
, protocol_version
, endpoint_id
, encrypt_option
, auth_scheme
, node_affinity
, num_reads
, num_writes
, last_read
, last_write
, net_packet_size
, client_net_address
, client_tcp_port
, local_net_address
, local_tcp_port
, connection_id
, parent_connection_id
, most_recent_sql_handle
, SUSER_NAME()
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
END
END;