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

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

USE [master]GO--1. 创建登录日志表IF OBJECT_ID('sqlserver_login_log') IS NOT NULL DROP TABLE sqlserver_login_logGOCREATE 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')BEGINDROP TRIGGER [trig_SERVER_login] ON ALL SERVERENDGO--创建登录触发器CREATE TRIGGER trig_SERVER_login  ON ALL SERVER --WITH EXECUTE AS 'login_test'  FOR LOGON  AS  BEGININSERT 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     ENDGO  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