Need help desperately

Question

Need help desperately

Hall of Fame

Points: 3239

March 24, 2023 at 10:04 pm

I accidentally ran this on a dev server which has locked my acct and I am unable to drop the trigger. Can someone help please?

--Create the dbo.ServerLogonHistory Table
CREATE TABLE dbo.ServerLogonHistory
    (
                EventType   VARCHAR(512),
                PostTime    DATETIME,
                SPID        INT,
                ServerName  VARCHAR(512),
                LoginName   VARCHAR(512),
                LoginType   VARCHAR(512),
                SID         VARCHAR(512),
                ClientHost  VARCHAR(512),
                IsPooled    BIT
    )
GO

--Grant insert rights to public for this table
GRANT INSERT ON dbo.ServerLogonHistory TO PUBLIC

--Create the Logon Trigger Trigger_ServerLogon
CREATE TRIGGER Trigger_ServerLogon ON ALL SERVER FOR LOGON
AS
BEGIN
    DECLARE @data XML
    SET @data = EVENTDATA()
    INSERT INTO dbo.ServerLogonHistory
    SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(512)')
                , @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
                , @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(4)')
                , @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)')
                , @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)')
                , @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)')
                , @data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)')
                , @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)')
                , @data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'nvarchar(1)')
END
GO

Viewing 2 posts - 1 through 1 (of 1 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

You need to connect to the DAC session via SQLCMD or SSMS and then drop the trigger.

ADMIN:MyServer\MyInstance

DROP TRIGGER Trigger_ServerLogon ON ALL SERVER