谨慎使用,当时我测试的时候就出现问题,结果导致无法登陆数据库。

使用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/