CODEWITHSUNDEEP
sqlsql-server
Mikhail
Mikhail

Reputation: 103

Bypass Logon trigger (doesn't let into the system)

I Created a logon trigger for training purposes. I wanted to make sure that SQL Server would not let a specific user. As a result, I can’t log in myself :( Naturally, I always logged in under Windows authentication, only after I wrote the trigger I set a mixed one. I didn’t set the 'sa' password and I don’t know. I connected in minimum configuration mode (-f), but I still can’t kill this TRIGGER Help.

CREATE TABLE Examples.LoginLog
(
LoginLogId int NOT NULL IDENTITY(1,1) CONSTRAINT PKLoginLog PRIMARY KEY,
LoginName sysname NOT NULL,
LoginTime datetime2(0) NOT NULL ,
ApplicationName sysname NOT NULL
);

CREATE TRIGGER LogonTrigger
ON ALL SERVER
WITH EXECUTE AS 'LogonTriggerLogging'
FOR LOGON
AS
IF ORIGINAL_LOGIN() = 'Login_NotAllowed'
   THROW 50000,'Unauthorized Access',1;
ELSE
   INSERT INTO MyDB.Examples.LoginLog(LoginName,LoginTime,ApplicationName)
   VALUES (ORIGINAL_LOGIN(),SYSDATETIME(),APP_NAME());

CREATE LOGIN Login_NotAllowed WITH PASSWORD = 'PASSWORD$1'

I can’t go under anyone else. Neither under LogonTriggerLogging, nor under Login_NotAllowed (which is understandable), nor under sa, nor under Windows authentication. Sql Server swears and says that the trigger is working, and that it will not let me go. Error Number 17892

Upvotes: 0

Views: 866

Answers (1)

lptr
lptr

Reputation: 6808

connect to the dac

in ssms (new query), servername = ADMIN:sqlinstancename

and disable the trigger

disable trigger LogonTrigger on all server

Upvotes: 1

Related Questions