none
请问这个登录触发器为什麽会阻止SQL的所有登录用户登录包括sa RRS feed

  • 问题

  • sql版本:sql2005  系统:Windows7

    -----------------------------------------------------------------------------------------
    --创建一个登录触发器审核登录事件
    --------------------------------------------------------
    
    
    --------/***************
    --------演示数据库和审核表
    --------3w@live.cn
    --------*******************/
    
    CREATE DATABASE ExampleAuditDB
    GO
    USE ExampleAuditDB
    GO
    
    CREATE TABLE dbo.RestrictedLogonAttempt
    (LoginNM sysname NOT NULL,
    AttemptDT DATETIME NOT NULL)
    GO
    
    --------/***************
    --------创建登录触发器
    --------3w@live.cn
    --------*******************/
    
    CREATE TRIGGER trg_logon_attempttest
    ON ALL SERVER
    WITH EXECUTE AS'sa'  
    FOR LOGON,ALTER_LOGIN
    AS
    BEGIN
    INSERT ExampleAuditDB.dbo.RestrictedLogonAttempt
    (LoginNM, AttemptDT)
    VALUES (ORIGINAL_LOGIN(), GETDATE())
    exec master.. xp_cmdshell 
    'bcp " select * from ExampleAuditDB.dbo.RestrictedLogonAttempt  " queryout d:\Logondata.txt -c -T'  -- /T参数可信连接
    
    END
    GO
    
    --------/***************
    --------查看审核记录
    --------3w@live.cn
    --------*******************/
    USE ExampleAuditDB
    GO
    SELECT * from dbo.RestrictedLogonAttempt
    go
    
    --------/***************
    --------删除演示数据库及演示触发器
    --------3w@live.cn
    --------*******************/
    USE [ExampleAuditDB]
    go
    
    DROP TRIGGER trg_logon_attempt
    ON ALL SERVER
    go
    
    DROP database ExampleAuditDB
    go
    


    本来想实现需求:记录登录到我们业务数据库的用户,写日志,因为之前有人改过登录用户密码sa的密码,经理要求要记录日志谁登录过

    我想记录非Windows用户登录的信息,即不是使用Windows验证方式登录的,用sql验证登录的要记录登录信息,比如sa登录过就要记录

    例外一个就是 修改登录用户密码的动作要记录

    我现在用bcp命令来写日志的,不知道大家还有没有其他写日志的好方法

    我先把登录信息写到一张表,然把表内容用bcp导出到一个txt文件

    最后一个大问题就是,现在执行了上面的脚本,连sql所有登录用户的登录不了,sa都不行

    难道要重装sql?


    给我写信: QQ我:点击这里给我发消息

    2013年2月6日 1:15

答案

  • 至于写文件, 通常写了表, 就不考虑写文件了吧, 就算要写, 也建议是增量的写, 不要用 bcp 这种每次全部写入的方式, 不然对于一个频繁使用的 sql server 来说, 开销是很大的

    在触发器在写文件, 可以考虑写个 CLR 函数, 这样共用当前连接上下文, 不至于产生再次登录的问题

    或者考虑不用 bcp 这种会产生登录的行为, 可以考虑使用类似下面这样的增量记录方式

    CREATE TRIGGER trg_logon_attempttest
    ON ALL SERVER
    WITH EXECUTE AS'sa'  
    FOR LOGON,ALTER_LOGIN
    AS
    BEGIN
    --INSERT ExampleAuditDB.dbo.RestrictedLogonAttempt
    --(LoginNM, AttemptDT)
    --VALUES (ORIGINAL_LOGIN(), GETDATE())
    DECLARE
    	@cmd nvarchar(4000)
    ;
    SELECT
    	@cmd = 'ECHO '
    		+ ORIGINAL_LOGIN()+ CHAR(9) + CONVERT(varchar(100), GETDATE(), 121)
    		+ ' >> d:\Logondata.txt'
    ;
    DECLARE @tb_re TABLE(re varchar(4000));
    INSERT @tb_re exec master.. xp_cmdshell @cmd
    END
    GO

    2013年2月6日 2:00
  • 这种情况通过管理员通道可以登录的

    命令提示符下用: sqlcmd -A

    或者是managent studio 登录窗口的服务器名称中输入: admin:xxxx

    其中 xxxx 为计算机名(默认实例), 或者 计算机名\实例名 (命名实例)

    2013年2月6日 5:30

全部回复

  • 你这个是死循环吧?

    xp_cmdshell 那里面的 bcp 会产生一个登录, 这导致再次进入触发器, 而触发器里面再 bcp 产生一个登录, ....

    2013年2月6日 1:30
  • 如果仅仅是为了记录登录成功/失败这类的登录信息, 你可以直接在 sql server 实例属性--安全性里面设置一下审核选项, 默认是记录失败登录 (这个记录会记录在 sql server 日志里面)

    另一种, 可以使用服务器审核规范 (Audit_Login_group)

    还可以通过事件探查器去Trace (Security Audit 中的相关事件)

    比较推荐的一种是事件通知

    2013年2月6日 1:43
  • 至于写文件, 通常写了表, 就不考虑写文件了吧, 就算要写, 也建议是增量的写, 不要用 bcp 这种每次全部写入的方式, 不然对于一个频繁使用的 sql server 来说, 开销是很大的

    在触发器在写文件, 可以考虑写个 CLR 函数, 这样共用当前连接上下文, 不至于产生再次登录的问题

    或者考虑不用 bcp 这种会产生登录的行为, 可以考虑使用类似下面这样的增量记录方式

    CREATE TRIGGER trg_logon_attempttest
    ON ALL SERVER
    WITH EXECUTE AS'sa'  
    FOR LOGON,ALTER_LOGIN
    AS
    BEGIN
    --INSERT ExampleAuditDB.dbo.RestrictedLogonAttempt
    --(LoginNM, AttemptDT)
    --VALUES (ORIGINAL_LOGIN(), GETDATE())
    DECLARE
    	@cmd nvarchar(4000)
    ;
    SELECT
    	@cmd = 'ECHO '
    		+ ORIGINAL_LOGIN()+ CHAR(9) + CONVERT(varchar(100), GETDATE(), 121)
    		+ ' >> d:\Logondata.txt'
    ;
    DECLARE @tb_re TABLE(re varchar(4000));
    INSERT @tb_re exec master.. xp_cmdshell @cmd
    END
    GO

    2013年2月6日 2:00
  • I look at it from another angle: if someone is able to get on to sql and change sa password, that one can disable your trigger then delete entries from your auditing db. 
    2013年2月6日 2:56
  • 那么现在登录不了,有没有办法解决呢邹建大侠

    给我写信: QQ我:点击这里给我发消息

    2013年2月6日 4:32
  • 这种情况通过管理员通道可以登录的

    命令提示符下用: sqlcmd -A

    或者是managent studio 登录窗口的服务器名称中输入: admin:xxxx

    其中 xxxx 为计算机名(默认实例), 或者 计算机名\实例名 (命名实例)

    2013年2月6日 5:30
  • 我之前在A电脑上用DAC连接过,不行,还有一个就是sql agent都被停止了,不能启动

    昨天我又在B电脑上做一次,结果连B电脑都遭殃了

    DAC连接 和sql agent跟A电脑一样

    难道只能重装B电脑?


    给我写信: QQ我:点击这里给我发消息

    2013年2月6日 7:24
  • 上图上错了,现在点击新建查询就可以连接,但是如果要打开对象资源管理器就出现下图


    给我写信: QQ我:点击这里给我发消息

    2013年2月6日 7:31
  • 搞定了,在新建查询里删除了那个触发器

    USE MASTER
    GO
    DROP TRIGGER trg_logon_attempttest ON ALL SERVER
    GO
    感谢邹建大侠拔刀相助


    给我写信: QQ我:点击这里给我发消息


    2013年2月6日 7:32
  • 最后问一下

    非Windows验证的方式要记录,Windows验证的登录方式不用记录,可以实现吗

    例外一个就是 修改登录用户密码的动作要记录


    给我写信: QQ我:点击这里给我发消息

    2013年2月6日 8:07