I am going to wrap up my discussion of LOGON TRIGGERS. The number and types of database triggers available to database administrators has increased over the years as the Microsoft SQL Server database engine has matured.
There are now triggers for execution after both DDL and DML modifications. These triggers can be classified as AFTER – TSQL code to be executed after an action or INSTEAD OF – TSQL code to be executed in lue of a action.
Triggers are commonly used in following tasks:
- Auditing data changes by place point in time copies into a log table.
- Stopping unwanted logins by time, user or application.
- Enforcing business rules by checking the existence of preconditions.
- Replicating data for historical tracking or data aggregation.
- Creating verticle data partitions for tables with large numbers of columns.
- Preventing unwanted changes by rolling back.
Today, I would like to focus on LOGON TRIGGERS. Some real world examples are to preventing users for accessing the server during off hours, preventing an Application User Id from accessing the server, and preventing certain applications, such as MS ACCESS, from connecting to the server.
Data Definition Language statements that support the definition or declaration of database objects. The following statments are used to define, change, and destroy triggers.
CREATE TRIGGER
ALTER TRIGGER
DROP TRIGGER
Unlike other objects, triggers can be DISABLE TRIGGER before and ENABLE TRIGGER after data processing events such as BULK IMPORT. The ALTER TABLE statement can be used to disable or enable ALL triggers on a particular table.
The AUDIT_SERVER_LOGINS example loads a table in MSDB with entries every time a user or process logs onto the server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
-- -- Audit logins -- -- Select msdb database USE MSDB; GO -- Delete existing schema. IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Audit') DROP SCHEMA [Audit] GO -- Add new schema. CREATE SCHEMA [Audit] AUTHORIZATION [dbo] GO -- Create login audit table CREATE TABLE [Audit].[LogDatabaseLogins]( [LoginId] BIGINT IDENTITY(1, 1) NOT NULL, [LoginDate] [datetime] NOT NULL, [LoginName] [varchar](20) NOT NULL, [AppName] [nvarchar](128) NOT NULL, [HostName] [nvarchar](128) NOT NULL, CONSTRAINT [pk_Ldl_LoginId] PRIMARY KEY CLUSTERED ([LoginId] ASC) ) ON [PRIMARY] GO -- Add defaults for key information ALTER TABLE [Audit].[LogDatabaseLogins] ADD CONSTRAINT [df_Ldl_LoginDate] DEFAULT (getdate()) FOR [LoginDate]; ALTER TABLE [Audit].[LogDatabaseLogins] ADD CONSTRAINT [df_Ldl_LoginName] DEFAULT (coalesce(suser_sname(),'?')) FOR [LoginName]; ALTER TABLE [Audit].[LogDatabaseLogins] ADD CONSTRAINT [df_Ldl_AppName] DEFAULT (coalesce(APP_NAME(),'?')) FOR [AppName]; ALTER TABLE [Audit].[LogDatabaseLogins] ADD CONSTRAINT [df_Ldl_HostName] DEFAULT (coalesce(HOST_NAME(),'?')) FOR [HostName]; GO -- Remove existing logon trigger IF EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'AUDIT_SERVER_LOGINS') DROP TRIGGER [AUDIT_SERVER_LOGINS] ON ALL SERVER GO -- Create new logon trigger CREATE TRIGGER AUDIT_SERVER_LOGINS ON ALL SERVER FOR LOGON AS BEGIN -- Add to the audit table INSERT INTO [msdb].[Audit].[LogDatabaseLogins] (LoginName, LoginDate) VALUES (ORIGINAL_LOGIN(), GETDATE()); END; GO |
The screen shot on the right shows records in the Log Database Logins table for my user id as well as the Red Gate toolset.
The DISALLOW_USERS example prevents users from connecting to the database between 11 pm and 7 am. The only user that can connect is the sa account. Please note, this is different than NT USERS who are part of the sysadmins SERVER ROLE. One way to elude this trigger if you get logged out is to change the date/time on the server thru a remote terminal session.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-- -- Prevent user logins during maintenance -- -- Remove existing logon trigger IF EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'DISALLOW_USERS') DROP TRIGGER [DISALLOW_USERS] ON ALL SERVER GO -- Create new logon trigger CREATE TRIGGER DISALLOW_USERS ON ALL SERVER FOR LOGON AS BEGIN -- Perform maintenance between 11 PM and 7 AM IF ( (ORIGINAL_LOGIN() <> 'sa') AND (DATEPART(HOUR, GETDATE()) IN (23, 0, 1, 2, 3, 4, 5, 6)) ) ROLLBACK; END; GO |
The screen shot on the right shows my user account getting rejected due to time of day.
The DISALLOW_APPID example prevents an application user id from connecting to the server. I did this example for completeness; However, a better way to do this is disable the USER in the security logins section of the server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-- -- Prevent application id login -- -- Remove existing logon trigger IF EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'DISALLOW_APPID') DROP TRIGGER [DISALLOW_APPID] ON ALL SERVER GO -- Create new logon trigger CREATE TRIGGER DISALLOW_APPID ON ALL SERVER FOR LOGON AS BEGIN -- STOP FORD_USER - FROM OBJECT OWNERSHIP TALK IF (ORIGINAL_LOGIN() = 'FORD_USER') ROLLBACK; END; GO |
The screen shot on the right shows the FORD_USER account getting rejected because of its name.
The current version of SQL Server gives the DBA many choices to enforce business rules and consistency. Please remember that most TRIGGERS are executed AFTER the action. Therefore, a SERVER trigger that stops a DROP DATABASE by ROLLBACK, might not be the correct action for a 10 terabyte database. On the other hand, if the database has recipe information and is only 10 megabytes in size, it might be the correct action. Many times granting rights using the principle of least privledge will prevent unwanted actions.
Next time, I am going to diverge a little by showing you how to create a ODBC connection and connect to the database via MS ACCESS. This talk will be a foundation for the last example on LOGON TRIGGERS, preventing unwanted applications from connecting to the server.
I recently arrived across your write-up and have been examining along. I wish to express my admiration of your crafting skill and ability to make site visitors read from the commencing to the end. I would like to read newer posts and to write about my thoughts with you.