使用SQL Server Log On trigger: CREATE DATABASE AuditDb GO USE AuditDb GO /* Create AuditTable */ CREATE TABLE ServerLogonHistory ( SystemUser VARCHAR ( 512 ), DBUser VARCHAR ( 512 ), SPID INT , LogonTime DATETIME ) GO /* Create LogonTrigge
使用SQL Server Log On trigger:
CREATE DATABASE AuditDb
GO
USE AuditDb
GO
/* Create AuditTable */
CREATE TABLE ServerLogonHistory
(SystemUser VARCHAR(512),
DBUser VARCHAR(512),
SPID INT,
LogonTime DATETIME)
GO
/* Create LogonTrigger */
CREATE TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
END
GO
用户登录后查看监控表：
因为我们可以监控到用户登录SQL Server的情况，所以我们就可以用户的访问，参考下面的文章：How to prevent users from accessing SQL Server from any application or any login expect your main application & its login：http://thesqldude.com/2012/04/07/how-to-prevent-users-from-accessing-sql-server-from-any-application-or-any-login-expect-your-main-application-its-login/
下载本文