I recently saw a post on StackExchange where a user was having a problem with tempdb filling up, which was causing their logon trigger to fail.

The logon trigger was attempting to prevent users from connecting with SQL Server Management Studio (SSMS). It seems that the user was not aware that it is trivial to connect using SSMS to SQL bypassing the app level restriction in place.

Let’s look at an example.

First we’ll create the logon trigger that explicitly looks to see if the application attempting to logon is management studio:

CREATE TRIGGER [DenySSMSLogin] ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
SET NOCOUNT ON;
DECLARE @app SYSNAME = APP_NAME();
IF @app LIKE N'%Management Studio%'
BEGIN
THROW 51000, 'Connection not allowed.', 1;
END;
END;
GO

ENABLE TRIGGER [DenySSMSLogin] ON ALL SERVER;
GO

Now we can test this by opening a new window. We should see an error:

Looks like it’s working just fine.

So let’s circumvent this, try connecting again, this time we just need to edit the Additional Connection Parameters in the connection dialog and pass along a new application name.

And now the connection will work just fine, completely bypassing the trigger (if trying this, don’t forget to disable your trigger when done).