sqlserver登陆日志审计

---方法1

--drop table loginlog
use master
go
create table loginlog
(id bigint identity(1,1) primary key
,loginname varchar(50) null
,hostname varchar(100) null
,spid int null
,app varchar(500) null
,elsedata varchar(max) null
,logindate datetime default getdate())
go

--drop trigger tri_loginlog on all server
use master
go
create trigger tri_loginlog
on all server with execute as 'sa'
for logon
as
begin
declare @data xml=EVENTDATA()
declare @IsPooled int
set @IsPooled = @data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'int')
if exists(select * from sys.databases where name='master') and (@IsPooled=1) and exists(select * from sys.tables where name='loginlog')
begin
insert into [master].dbo.loginlog(loginname,hostname,spid,app,elsedata)
values(ORIGINAL_LOGIN(),HOST_NAME(),@@SPID,APP_NAME(),convert(varchar(max),@data))
end
end

-----select * from sys.server_triggers

 ---方法2

/*登录日志记录表*/
CREATE TABLE SYS_LOGIN_LOG(
ID INT IDENTITY
, POSTTIME DATETIME
, EVENTTYPE VARCHAR(100)
, SERVERNAME VARCHAR(50)
, CLIENTHOST VARCHAR(100)
, LOGINNAME VARCHAR(100)
, CONSTRAINT PK_SYS_LOGIN_LOG PRIMARY KEY(ID)
)
GO
/*数据库登录审计触发器*/
create trigger sys_login_tri
on all server
for LOGON
as
declare @data xml,
@PostTime datetime,
@EventType varchar(100),
@ServerName varchar(100),
@ClientHost varchar(100),
@LoginName varchar(100),
@LoginType varchar(100),
@ip varchar(60)

set @data=EVENTDATA()
set @PostTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
set @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
set @ServerName = @data.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(100)')
set @ClientHost = @data.value('(/EVENT_INSTANCE/ClientHost)[1]','varchar(100)')
set @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(100)')
set @ip=@data.value ('(/EVENT_INSTANCE/ClientHost)[1]','VARCHAR(60)')
insert into sys_login_log(PostTime, EventType, ServerName, ClientHost, LoginName)
values (@PostTime,@EventType,@ServerName,@ClientHost,@LoginName)

/*ip访问限制[可选]*/
if ((@ip!='127.0.0.1')
and (@ip!='<local machine>')
and (not exists(select 1 from V_IP_TABLES where ip=@ip)) )
BEGIN
rollback
RAISERROR (N'Can''t connect to server, IP not allowed: %s', -- Message text.
16, -- Severity,
1, -- State,
@ip)
WITH NOWAIT;
END

GO