Key Points Of Logon Trigger

SQL Server has a useful utility which is logon trigger. But if you don't be careful enough it can be very dangerous, even SQL Server instance may be out of service.

You can create a logon trigger named [LogonTrigger] as follows. 
Create TRIGGER [LogonTrigger]
ON ALL SERVER 
FOR LOGON
AS
BEGIN
            ....
           return
END

It can be written any code between BEGIN and END scope. Take into consideration the following points.

• Before enabling logon trigger, enable DAC, in case if any thing goes wrong.
• Try to not access any user defined database sources. Because  when the user database fails, then logon trigger fails, when logon trigger fails, then SQL Server instance fails. This means SQL Sever instance will be out of service.
• Since this logon trigger will be executed on every logon, code must be simple and it's cost must be low.
• In order to save sysadmin logon, the code below can be the very fist line of your trigger.
  
  if issrvrolemember('sysadmin') = 1
       return
• The other services like SQL Server Agent, is depended on SQL Servere service. Agent service logons database engine while starting up. If logon fails due to logon trigger, agent service will not start up. Be careful that this logon process is not blocked.
• If SQL Server is a busy server and applications runs disconnectedly, logon  trigger may be a headache. Try avoiding to use logon trigger on those kind of servers.

Before running logon trigger on production servers, test it on test servers, including restarting database engine. Be sure that every thing is ok. 

With logon trigger you may want to solve some issues but you may ruin it be careful.