As I get older I have come to enjoy watching others fish, especially my children. The thrill of catching a big fish is magnified by seeing the smile on someone else’s face when he/she is the one bringing it in. Below is a nice sized largemouth bass my son caught on a recent fishing trip.
In my previous post I showed how to create a login trigger to log sysadmin access to a SQL Server instance. Almost immediately I received a comment describing how the failure of the trigger could almost completely prevent anyone from logging into the instance. This is a major problem!
The reason this occurs makes sense if you think about it. While attempting to login, the user executes some code in a trigger. If that code is invalid, the trigger will fail and abort. When that happens, the login aborts as well. What could cause the trigger to fail? Well, if the table (or other objects) you are accessing within the trigger is inaccessible to the user, or if it doesn’t even exist, the trigger will fail.
I tested this by using my working trigger, which logged sysadmin logins to a table called dbo.sysadminLogging. Next I renamed the table to dbo.sysadminLogging1.
Next I tried to login in a new window in SSMS:
First, let’s talk about how to get back into a server that has this issue. We need to log into the SQL using SQLCMD with a dedicated administrator connection, then disable the trigger:
After doing this everyone should now be able to log back into SQL Server as normal.
Now to prevent this type of event from happening, I suggest a small edit to my original trigger. This edit will make sure the referenced objects are valid. If not, the trigger does nothing. It may also be a good idea to send an email to the DBA so they can investigate, and I’ve noted that in the comments.
CREATE TRIGGER [servertrigger_CheckForSysAdminLogin] ON ALL SERVER FOR LOGON AS BEGIN IF OBJECT_ID('DBMaint.dbo.sysadminLogging') IS NULL BEGIN --Possibly send an email to the DBA, indicating the trigger is not working as expected GOTO Abort;--Do nothing END IF IS_SRVROLEMEMBER('sysadmin') = 1 BEGIN INSERT INTO DBMaint.dbo.sysadminLogging ( [Login] , LoginDate ) VALUES ( ORIGINAL_LOGIN() , GETDATE() ); END; Abort: END; GO
This newer version of the trigger should cut down on the chances that this functionality will come back to bite you. Special thanks to james youkhanis for pointing this out.