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?