审计sqlserver ip和主机名

发布时间：2020-12-12 13:18:50 所属栏目：MsSql教程来源：网络整理

导读：declare @ip varchar(200) select @ip='' declare @num int select @num=1 while(@num=1) begin SELECT @ip=b.[client_net_address] FROM MASTER.sys.dm_exec_sessions a? INNER JOIN MASTER.sys.dm_exec_connections b? ON a.session_id=b.session_id? and

declare @ip varchar(200)
select @ip=''
declare @num int
select @num=1
while(@num=1)
begin
SELECT @ip=b.[client_net_address]
FROM MASTER.sys.dm_exec_sessions a?
INNER JOIN MASTER.sys.dm_exec_connections b?
ON a.session_id=b.session_id?
and a.[host_name]='0.0.0.0'
--and a.[host_name]='book3'
if @@rowcount>0
begin
print @ip
select @num=0
end

end

select b.[session_id],b.[login_time],b.[host_name],b.[original_login_name],a.[client_net_address],a.TEXT?
from ?MASTER.sys.dm_exec_sessions b,
(
SELECT SESSION_ID,[client_net_address],TEXT
FROM
?SYS.DM_EXEC_CONNECTIONS
CROSS APPLY
?SYS.DM_EXEC_SQL_TEXT(MOST_RECENT_SQL_HANDLE)) a where a.SESSION_ID=b.SESSION_ID and a.TEXT like 'select%'

----------------------------------------------------------------------------------------------------------------------------

创建IP和主机名对应关系表

USE [xxxxxxx]
GO

CREATE TABLE [dbo].[audit_host](
[host_name] [nvarchar](128) NULL,
[client_net_address] [varchar](48) NULL
) ON [PRIMARY]

GO

一秒钟执行一次新连接插入audit_host表

declare @num int select @num=1 while(@num=1) begin insert into audit_host select * from (SELECT? distinct a.[host_name],b.[client_net_address]? FROM MASTER.sys.dm_exec_sessions a WITH(NOLOCK) INNER JOIN MASTER.sys.dm_exec_connections b WITH(NOLOCK) ON a.session_id=b.session_id) c where not exists (select 1 from audit_host d where c.[host_name]=d.[host_name] and c.[client_net_address]=d.[client_net_address]) WAITFOR DELAY '00:00:01' end

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!