用服务器触发器统计登录情况

参考： msdn   CREATE TRIGGER (Transact-SQL)     登录触发器

USE [master]
GO
--1. 创建登录日志表
IF OBJECT_ID('sqlserver_login_log') IS NOT NULL 
	DROP TABLE sqlserver_login_log
GO
CREATE TABLE sqlserver_login_log(
	id INT IDENTITY(1,1) PRIMARY KEY,
	loginame VARCHAR(50) NOT NULL,
	loginTime DATETIME NOT NULL DEFAULT(GETDATE())
)
GO
--2. 删除触发器，再创建触发器
IF EXISTS(SELECT 1 FROM MASTER.sys.server_triggers AS st WHERE st.name='trig_SERVER_login')
BEGIN
	DROP TRIGGER [trig_SERVER_login] ON ALL SERVER	
END
GO
--创建登录触发器
CREATE TRIGGER trig_SERVER_login  
ON ALL SERVER 
--WITH EXECUTE AS 'login_test'  
FOR LOGON  
AS  
BEGIN
	INSERT INTO sqlserver_login_log(loginame)
	VALUES(ORIGINAL_LOGIN());
END;  
GO
--3. 创建测试账号
IF EXISTS(SELECT * FROM sys.syslogins AS s WHERE NAME='login_test')  
BEGIN  
    DROP LOGIN login_test     
END
GO  
CREATE LOGIN login_test WITH PASSWORD=N'login_test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
IF EXISTS(SELECT 1 FROM sys.database_principals WHERE NAME='login_test' AND type_desc='SQL_USER')  
    DROP USER login_test  
GO  
CREATE USER login_test FOR LOGIN login_test  
GO
--4. 给测试账号授权
--   特别需要注意的是：账号必须对日志表有 insert 权限！否则将无法登录
GRANT VIEW SERVER STATE TO login_test;
GRANT select,INSERT ON master.dbo.sqlserver_login_log TO login_test;
GO

--在相关账号登录之后，可以查到登录的信息
SELECT * FROM sqlserver_login_log AS s