SELECT a.[session_id],a.[login_time],a.[host_name], a.[original_login_name],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这里的host_name也可以获得机器名,做个黑名单即可
create TRIGGER myTest_LogonTrigger ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN declare @LogonTriggerData xml, @EventTime datetime, @LoginName varchar(50), @HostName varchar(50), @LoginType varchar(50) set @LogonTriggerData = eventdata()
set @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') set @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)') set @HostName = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)') set @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)')
if @LoginType = 'SQL Login' and @HostName not in ('<comma separated list of hostname>') and @LoginName not in ('<comma separated list of SQL logins>') insert into master..myTest values (@EventTime, @LoginName, @HostName, @LoginType) end go
a.[session_id],a.[login_time],a.[host_name],
a.[original_login_name],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这里的host_name也可以获得机器名,做个黑名单即可
如果是进来了数据库能自动判断机器名对比,如果不是该机器名或mac地址就自动结束会话也行
(LogonTime datetime,
LoginName varchar(50),
ClientHost varchar(50),
LoginType varchar(50)
)
create TRIGGER myTest_LogonTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
declare @LogonTriggerData xml,
@EventTime datetime,
@LoginName varchar(50),
@HostName varchar(50),
@LoginType varchar(50)
set @LogonTriggerData = eventdata()
set @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
set @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
set @HostName = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
set @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)')
if @LoginType = 'SQL Login' and @HostName not in ('<comma separated list of hostname>')
and @LoginName not in ('<comma separated list of SQL logins>')
insert into master..myTest values (@EventTime, @LoginName, @HostName, @LoginType)
end
go