Various Triggers – Part 2

I am going to wrap up my discussion of LOGON TRIGGERS. We now have a MS ACCESS database that is using a LINKED TABLE pointing to the [WILDLIFE] SQL Server database.

Most professional applications set the application name string before connecting via ODBC API to a data source. MS ACCESS does just that at a higher level, the product suite name.

The trigger named ‘DISALLOW_MS_OFFICE’ uses the APP_NAME() system function to determine if the application is MS OFFICE product. We are going to revoke all MS OFFICE products from connecting to the server using the TSQL below.

--
-- Stop MS Office Users
--

-- Remove existing logon trigger
IF  EXISTS (SELECT * FROM master.sys.server_triggers 
WHERE parent_class_desc = 'SERVER' AND name = N'DISALLOW_MS_OFFICE')
  DROP TRIGGER [DISALLOW_MS_OFFICE] ON ALL SERVER
GO


-- Create new logon trigger
CREATE TRIGGER DISALLOW_MS_OFFICE
ON ALL SERVER EXECUTE AS 'sa' FOR LOGON
AS
BEGIN
    -- Account for case sensitive collations
    IF LOWER(APP_NAME()) LIKE '%microsoft office system%' 
        ROLLBACK;
END;

The screen shot below shows the SERVER level triggger are enabled.

I am going to open the MS ACCESS database named [WILDLIFE.accdb] and open the linked table named [dbo_ANIMALS]. The screen shot below shows the application being rejected by the LOGON TRIGGER on the server.

In summary, LOGON TRIGGERS can be used to audit user logins, prevent user logins, prevent application logins, and restrict the hours in which logins can occur. Next time, I am going to start talking about how DML AFTER TRIGGERS which can be used to audit or prevent data changes.