Sql server跟踪IP记录访问
1、第一步、新建数据库
create database test
2、第二步、新建跟踪表
select
c.session_id,most_recent_session_id,connect_time,net_transport,protocol_type,
protocol_version,c.endpoint_id,c.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,most_recent_sql_handle,login_time,
host_name,program_name,host_process_id,client_version,client_interface_name,security_id,
login_name,nt_domain,nt_user_name,status,context_info,cpu_time,memory_usage,total_scheduled_time,
total_elapsed_time,s.endpoint_id as s_endpoint_id,last_request_start_time,last_request_end_time,reads,writes,logical_reads,text_size
into tracetable
from sys.dm_exec_connections c JOIN sys.dm_exec_sessions s ON s.session_id = c.session_id;
3、第三步、新建作业
insert into tracetable
select
c.session_id,most_recent_session_id,connect_time,net_transport,protocol_type,
protocol_version,c.endpoint_id,c.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,most_recent_sql_handle,login_time,
host_name,program_name,host_process_id,client_version,client_interface_name,security_id,
login_name,nt_domain,nt_user_name,status,context_info,cpu_time,memory_usage,total_scheduled_time,
total_elapsed_time,s.endpoint_id as s_endpoint_id,last_request_start_time,last_request_end_time,reads,writes,logical_reads,text_size
from sys.dm_exec_connections c JOIN sys.dm_exec_sessions s ON s.session_id = c.session_id;
4、第四步、新建计划
注：select into from 和 insert into select都是用来复制数据，两者的主要区别为：select into from 要求目标表不存在，因为在插入时会自动创建，insert into select from 要求目标表存在
