SQL DDL Triggers

This post covers the details of creating a Data Definition Language (DDL) trigger, which are used to perform administrative tasks within the database or on the server itself.  For a useful jumping off point regarding DDL Triggers, see this TechNet article.

I have used such triggers in the past for forcibly logging a user off the system, when they attempt to access SQL server through the Management Studio.  Typically this is when a username and password that is used in an application is well known, and I only want that application to be able to connect, not a user via the Management Console.  Such a scenario would be better controlled via user permissions, however this is not always possible.

The following code is used to create a trigger (called “TR_LOGON_APP”).  This trigger occurs after the user has logged on, but before the session is created.  If the user is connecting with the Management Studio, an error is thrown, and the “This login is for application use only” error is entered into the SQL Log.

To create such a trigger:

• Run “sqlcmd” at a command prompt
• Copy and paste the code into the SQL command window
  [Note: Change the <username> section of the code to reflect the user you want to prevent logging on]

CREATE TRIGGER [TR_LOGON_APP]

ON ALL SERVER

FOR LOGON

AS

BEGIN

        DECLARE @program_name nvarchar(128)

                DECLARE @host_name nvarchar(128)

                SELECT @program_name = program_name,

                                @host_name = host_name

                FROM sys.dm_exec_sessions AS c

                WHERE c.session_id = @@spid

                

                IF ORIGINAL_LOGIN() IN('<username>')

                                AND @program_name LIKE '%Management%Studio%'

                BEGIN

                                RAISERROR('This login is for application use only.',16,1)

                                ROLLBACK;

                END

        END;

• Review the code, type GO, and press the enter/return key
• To see any triggers that have been created, type the following at the SQL command prompt:
  
          SELECT * FROM sys.server_triggers
          GO
• To delete a trigger, run the above command to list all DDL triggers, and make a note of the one you want to delete, and then type the following at the SQL command prompt:
  DROP TRIGGER <trigger name> ON ALL SERVER
  GO