异常连接数据库
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')
)
【版权声明】
本站部分内容来源于互联网，本站不拥有所有权，不承担相关法律责任。如果发现本站有侵权的内容，欢迎发送邮件至masing@13sy.com 举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。
上一篇： mongodb备份与恢复
下一篇： mongodb性能监控