Sql-server – TEMPDB full causing login problems with server login trigger

loginssql-server-2016trigger

We have a server trigger used to prevent some logins from accessing SQL Server via SSMS in our DEV environment. 

CREATE TRIGGER [Deny_SQLLogin_SSMS_Trigger]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @login    SYSNAME = ORIGINAL_LOGIN()
    DECLARE @app      SYSNAME = APP_NAME()
    DECLARE @hostname SYSNAME = HOST_NAME()

    -- is this login in our prohibited list.
    IF @login IN ('some_user', 'some_user2', 'some_user3') AND @app LIKE N'%Management Studio%'
    BEGIN
        -- yes so send back an error
        --ROLLBACK;

        -- .. and record the attempt.
        INSERT msdb.dbo.AuditLog([Login],App,HostName,EventTime)
        VALUES (@login, @app, @hostname, SYSUTCDATETIME());

        THROW 51000, 'Connection not allowed.', 1;  
    END
END

GO

ENABLE TRIGGER [Deny_SQLLogin_SSMS_Trigger] ON ALL SERVER
GO

This works just great when tempdb isn't full but when it is, all logins get rejected.

Message
Logon failed for login 'some_user' due to trigger execution. 


Message
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.

No one can login from anywhere and we are forced to restart the instance. This is not going to be possible for our production instances when we start using the login trigger there as well.

So how can we design the trigger so that logins don't get blocked in case tempdb fills up unexpectedly?

Best Answer

I would like to point out an another way to do this instead of the trigger. As @Nic pointed out in the comment, your solution does not prevent users from connecting through SSMS (as you can change the app name).

You should create SQL-logins for the application or use service accounts if you are using Active Directory and create windows logins for them. This is only solution that does not allow your denied logins to connect at all and it does not matter if tempdb is full.

Related Question