Post reply

Logon Trigger

  • June 20, 2012 at 5:15 am

    #262603

    Hi,

    I wanted to test a failing logon trigger and then recovering from it, in lieu of a trigger that's going into production soon. I created a logon trigger on my local instance > .\sql2005. It broke my SQL Server as planned and I can no longer connect to it via SSMS.

    So, I launch sqlcmd and get access through DAC. I then try and disable the trigger, but it just hangs and doesn't do anything. I've restarted SQL several times, but can't get the trigger to disable.

    Commands used:

    connect via DAC:

    sqlcmd -A -E -Slocalhost\sql2005 -dmaster

    check I'm connected using DAC:

    SELECT * FROM sys.dm_exec_connections ec join sys.endpoints e on (ec.endpoint

    _id=e.endpoint_id) where e.name='Dedicated Admin Connection' and session_id=@@sp

    id; - returns 1 row

    get trigger name:

    select name from sys.server_triggers;

    disable trigger:

    disable trigger Logon_Trigger ON ALL SERVER;

    then it just hangs.. doesn't do anything

    Any ideas?

    thanks, Andrew

  • June 20, 2012 at 6:03 am

    #1503349

    I don't see any reason why it should hang, sorry.

    Have you tried dropping the trigger?

    -- Gianluca Sartori

  • June 20, 2012 at 6:57 am

    #1503376

    Check what's holding locks (probably a sch-S) lock on the trigger (sys.dm_tran_locks), see if you can kill whatever that is.

    p.s. What was your broken login trigger?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 20, 2012 at 8:19 am

    #1503465

    Hi,

    Yes - I've tried dropping the trigger.

    Querying sys.dm_tran_locks brings back quite a few X and Sch-S locks for my trigger. I've tried killing the SPIDs, though twice as many spring back up again, like some evil SQL hydra.

    Thought that I'd start SQL in single user mode and see if anything could be done that way, but it says my "SQL Server installation is either corrupt or has been tampered with". Though, before this, it was working fine and I'm pretty sure this message was always there...

  • June 20, 2012 at 8:29 am

    #1503477

    for some reason it didn't like me starting it in single user mode via DOS. Starting it in single user mode via SSCM worked. Once connected via DAC using SQLCMD, there were no locks on my trigger (thanks Gail), and I was able to disable it.

    Worth while practice in the end...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply