Logon triggers are a powerful feature in SQL Server that allow you to perform actions whenever a user logs in to the database server. In this article, we will explore an interesting observation related to logon triggers and discuss how to solve a common problem that can occur when dropping a database with an active logon trigger.

Let’s start by creating a simple logon trigger that logs information about user logins to a separate audit database:

CREATE DATABASE AuditDb
GO
USE AuditDb
GO

CREATE TABLE ServerLogonHistory
(SystemUser VARCHAR(512),
DBUser VARCHAR(512),
SPID INT,
LogonTime DATETIME)
GO

CREATE TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER, USER, @@SPID, GETDATE()
END
GO

Now, if we drop the AuditDb database, we will encounter an error when trying to log in to the SQL Server:

USE master
GO
DROP DATABASE AuditDB
GO

The error message will be: “Logon failed for login ‘SQL\Pinal’ due to trigger execution.”

This error occurs because the logon trigger is still active even after the database has been dropped. To fix this problem, we need to drop the logon trigger before dropping the database:

USE master
GO
DROP TRIGGER Tr_ServerLogon ON ALL SERVER
GO

Now, we can log in to the SQL Server without any issues.

In some cases, you may encounter a situation where you are unable to log in to the SQL Server using SQL Server Management Studio after dropping a database with an active logon trigger. In such cases, you can use the Dedicated Administrator Connection (DAC) to fix the problem.

The DAC is a special connection that allows administrators to connect to a SQL Server instance even when regular connections are not available. To use the DAC, you can connect to the SQL Server using the sqlcmd utility with the -A option:

sqlcmd -S LocalHost -d master -A

Once connected, you can drop the logon trigger using the following command:

DROP TRIGGER Tr_ServerLogon ON ALL SERVER
GO

This will remove the logon trigger and allow you to log in to the SQL Server using SQL Server Management Studio again.

In conclusion, logon triggers are a powerful feature in SQL Server that can be used to perform actions whenever a user logs in to the database server. However, it is important to be aware of the potential issues that can arise when dropping a database with an active logon trigger. By following the steps outlined in this article, you can avoid these problems and ensure smooth operation of your SQL Server environment.

Thank you for reading. If you have any thoughts or questions about logon triggers in SQL Server, please let me know in the comments.