{"id":2867,"date":"2013-10-24T06:11:18","date_gmt":"2013-10-24T06:11:18","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2867"},"modified":"2013-10-24T06:21:55","modified_gmt":"2013-10-24T06:21:55","slug":"%e5%bc%82%e5%b8%b8%e8%bf%9e%e6%8e%a5%e6%95%b0%e6%8d%ae%e5%ba%93","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2013\/10\/24\/%e5%bc%82%e5%b8%b8%e8%bf%9e%e6%8e%a5%e6%95%b0%e6%8d%ae%e5%ba%93\/","title":{"rendered":"\u5f02\u5e38\u8fde\u63a5\u6570\u636e\u5e93"},"content":{"rendered":"
\r\n<\/pre>\n
\n
\r\nSELECT 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,<\/pre>\n<\/div>\n
\n\tCONVERT(VARCHAR(20),last_request_end_time,120) last_request_end_time,login_name,s.session_id<\/div>\n
FROM master.sys.dm_exec_sessions AS s (nolock)
\n\tINNER JOIN master.sys.dm_exec_connections AS c (nolock) ON s.session_id=c.session_id
\n\tINNER JOIN master.sys.databases db ON s.database_id = db.database_id
\n\tOUTER APPLY master.sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
\n\tLEFT JOIN dbo.mdSystemIP msi ON client_net_address = msi.IP
\n\tWHERE
\n\tNOT EXISTS( –\u975e\u9650\u5b9aIP\u8bbf\u95ee
\n\t\tSELECT 1 FROM dbo.mdSystemIP ms WHERE client_net_address = ms.IP
\n\t )
\n\t OR <\/p>\n
\t  ( –\u670d\u52a1\u5668\u8bbf\u95ee\u4f46\u662f\u975e\u5408\u6cd5\u5ba2\u6237\u7aef\uff0c\u5305\u542b\u7528ssms\u76f4\u8054\u4e5f\u662f\u4e0d\u5408\u6cd5
\n\t\tEXISTS(SELECT 1 FROM dbo.mdSystemIP ms WHERE client_net_address = ms.IP AND VisitType='Server')
\n\t\tAND program_name NOT LIKE '.Net SqlClient Data Provider%' –\u7a0b\u5e8f\u8fde\u63a5
\n\t\tAND program_name NOT LIKE 'SqlQuery%'  –\u7f13\u5b58\uff0c\u6570\u636e\u5e93\u4f9d\u8d56
\n\t\tAND program_name NOT LIKE 'SQLAgent%' –job
\n\t\tAND program_name NOT LIKE 'Repl%'  –\u590d\u5236\u5206\u53d1
\n\t\tAND program_name NOT LIKE 'DATABASE%'  –\u590d\u5236\u5206\u53d1
\n\t\tAND program_name NOT LIKE 'SQLCLUSTER%'  –\u590d\u5236\u5206\u53d1
\n\t\tAND program_name != 'Microsoft SQL Server' –\u81ea\u8eab\u6267\u884c
\n\t\tAND program_name != 'Microsoft® Windows® Operating System'
\n\t\tAND login_name != 'NT AUTHORITY\\SYSTEM'
\n\t\tAND db.NAME NOT IN('master','msdb','distribution')
\n\t )<\/p>\n","protected":false},"excerpt":{"rendered":"
SELECT CONVERT(VARCHAR(50),CONNECTIONPROPERTY('loca […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2867","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2867","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2867"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2867\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2867"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2867"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2867"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}