Contents

1 소스

2 참고

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;

--서버 트리거 disable
disable trigger [tigger_name] on all server;

--서버 트리거 drop
drop trigger [tigger_name] on all server;