SELECT CONVERT(VARCHAR(50),CONNECTIONPROPERTY('local_net_address'))host ,client_net_address,program_name,db.NAME,convert(varchar(max),t.[text]) SQLText,CONVERT(varchar(20),login_time,120) login_time,

FROM master.sys.dm_exec_sessions AS s (nolock)
INNER JOIN master.sys.dm_exec_connections AS c (nolock) ON s.session_id=c.session_id
INNER JOIN master.sys.databases db ON s.database_id = db.database_id
OUTER APPLY master.sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
LEFT JOIN dbo.mdSystemIP msi ON client_net_address = msi.IP
WHERE
NOT EXISTS( –非限定IP访问
SELECT 1 FROM dbo.mdSystemIP ms WHERE client_net_address = ms.IP
)
OR

( –服务器访问但是非合法客户端，包含用ssms直联也是不合法
EXISTS(SELECT 1 FROM dbo.mdSystemIP ms WHERE client_net_address = ms.IP AND VisitType='Server')
AND program_name NOT LIKE '.Net SqlClient Data Provider%' –程序连接
AND program_name NOT LIKE 'SqlQuery%' –缓存，数据库依赖
AND program_name NOT LIKE 'SQLAgent%' –job
AND program_name NOT LIKE 'Repl%' –复制分发
AND program_name NOT LIKE 'DATABASE%' –复制分发
AND program_name NOT LIKE 'SQLCLUSTER%' –复制分发
AND program_name != 'Microsoft SQL Server' –自身执行
AND program_name != 'Microsoft® Windows® Operating System'
AND login_name != 'NT AUTHORITY\SYSTEM'
AND db.NAME NOT IN('master','msdb','distribution')
)