sql登录触发器限制登录账户名及ip地址及登录时间

sql登录触发器限制登录账户名及ip地址及登录时间

转自 http://www.greensoftcode.net/techntxt/2011610235318377133663、

if   exists (SELECT * FROM  sys.server_triggers  where   name= 'logon_verif')
 Drop Trigger  logon_verif On All Server
go
CREATE TRIGGER logon_verif
ON ALL SERVER 
FOR LOGON
AS
BEGIN
declare @ip nvarchar(60)
declare @loginName nvarchar(60)
declare @timev int
set @ip=EVENTDATA().value ('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(60)')
set @loginName=EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(60)')
set @timev=cast( convert(varchar(2),getdate(),8) as int)
 if  ( (@loginName='shop') and  (@timev>18 or @timev<9  ))
  BEGIN
  ---if not exists(select 1 from ipTable where ip=@ip)
  rollback
  End
 if ( (@loginName='sa') and  (@ip!='127.0.0.1')and  (@ip!='<local machine>')and  (@ip!='218.25.20.72'))
   BEGIN 
  rollback
  End
end

   rollback
  End
end

•