{"id":1799,"date":"2012-03-22T20:35:57","date_gmt":"2012-03-22T20:35:57","guid":{"rendered":"http:\/\/craftydba.com\/?p=1799"},"modified":"2017-10-12T00:58:31","modified_gmt":"2017-10-12T00:58:31","slug":"various-triggers-part-1","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1799","title":{"rendered":"Various Triggers &#8211; Part 1"},"content":{"rendered":"<p><a href=\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/03\/plastic-tommy-gun.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/03\/plastic-tommy-gun-150x150.jpg\" alt=\"\" title=\"plastic-tommy-gun\" width=\"150\" height=\"150\" class=\"alignleft size-thumbnail wp-image-4106\" \/><\/a>I am going to wrap up my discussion of <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/bb326598.aspx\">LOGON TRIGGERS<\/a>.  The number and types of <a href=\"http:\/\/en.wikipedia.org\/wiki\/Database_trigger\">database triggers<\/a> available to database administrators has increased over the years as the Microsoft SQL Server database engine has matured.<\/p>\n<p>There are now triggers for execution after both <a href=\"http:\/\/en.wikipedia.org\/wiki\/Data_Definition_Language\">DDL<\/a> and <a href=\"http:\/\/en.wikipedia.org\/wiki\/Data_Manipulation_Language\">DML<\/a> modifications. These triggers can be classified as AFTER &#8211; TSQL code to be executed after an action or INSTEAD OF &#8211; TSQL code to be executed in lue of a action.<\/p>\n<p>Triggers are commonly used in following tasks:<\/p>\n<ul>\n<li>Auditing data changes by place point in time copies into a log table.<\/li>\n<li>Stopping unwanted logins by time, user or application.<\/li>\n<li>Enforcing business rules by checking the existence of preconditions.<\/li>\n<li>Replicating data for historical tracking or data aggregation.<\/li>\n<li>Creating verticle data partitions for tables with large numbers of columns.<\/li>\n<li>Preventing unwanted changes by rolling back.<\/li>\n<\/ul>\n<p>Today, I would like to focus on <span style=\"color: #ff0000;\">LOGON TRIGGERS<\/span>. 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.<\/p>\n<p>Data Definition Language statements that support the definition or declaration of database objects. The following statments are used to define, change, and destroy triggers.<\/p>\n<p><a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms189799.aspx\">CREATE TRIGGER<\/a><br \/>\n<a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms176072.aspx\">ALTER TRIGGER<\/a><br \/>\n<a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms173497.aspx\">DROP TRIGGER<\/a><\/p>\n<p>Unlike other objects, triggers can be <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms189748.aspx\">DISABLE TRIGGER<\/a> before and <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms182706.aspx\">ENABLE TRIGGER<\/a> after data processing events such as BULK IMPORT. The <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms190273.aspx\">ALTER TABLE<\/a> statement can be used to disable or enable ALL triggers on a particular table.<\/p>\n<p>The AUDIT_SERVER_LOGINS example loads a table in MSDB with entries every time a user or process logs onto the server.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"tsql - audit server logins\">\r\n--\r\n-- Audit logins\r\n--\r\n\r\n-- Select msdb database\r\nUSE MSDB;\r\nGO\r\n\r\n-- Delete existing schema.\r\nIF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Audit')\r\nDROP SCHEMA [Audit]\r\nGO\r\n\r\n-- Add new schema.\r\nCREATE SCHEMA [Audit] AUTHORIZATION [dbo]\r\nGO\r\n\r\n-- Create login audit table\r\nCREATE TABLE [Audit].[LogDatabaseLogins](\r\n\t[LoginId] BIGINT IDENTITY(1, 1) NOT NULL,\r\n\t[LoginDate] [datetime] NOT NULL,\r\n\t[LoginName] [varchar](20) NOT NULL,\r\n\t[AppName] [nvarchar](128) NOT NULL,\r\n\t[HostName] [nvarchar](128) NOT NULL,\r\n CONSTRAINT [pk_Ldl_LoginId] PRIMARY KEY CLUSTERED ([LoginId] ASC)\r\n) ON [PRIMARY]\r\nGO\r\n\r\n-- Add defaults for key information\r\nALTER TABLE [Audit].[LogDatabaseLogins] \r\n    ADD CONSTRAINT [df_Ldl_LoginDate] DEFAULT (getdate()) FOR [LoginDate];\r\nALTER TABLE [Audit].[LogDatabaseLogins] \r\n    ADD CONSTRAINT [df_Ldl_LoginName] DEFAULT (coalesce(suser_sname(),'?')) FOR [LoginName];\r\nALTER TABLE [Audit].[LogDatabaseLogins] \r\n    ADD CONSTRAINT [df_Ldl_AppName] DEFAULT (coalesce(APP_NAME(),'?')) FOR [AppName];\r\nALTER TABLE [Audit].[LogDatabaseLogins] \r\n    ADD CONSTRAINT [df_Ldl_HostName] DEFAULT (coalesce(HOST_NAME(),'?')) FOR [HostName];\r\nGO\r\n\r\n-- Remove existing logon trigger\r\nIF  EXISTS (SELECT * FROM master.sys.server_triggers \r\n    WHERE parent_class_desc = 'SERVER' AND name = N'AUDIT_SERVER_LOGINS')\r\nDROP TRIGGER [AUDIT_SERVER_LOGINS] ON ALL SERVER\r\nGO\r\n\r\n-- Create new logon trigger\r\nCREATE TRIGGER AUDIT_SERVER_LOGINS\r\nON ALL SERVER FOR LOGON\r\nAS\r\nBEGIN\r\n     -- Add to the audit table\r\n     INSERT INTO [msdb].[Audit].[LogDatabaseLogins] (LoginName, LoginDate)\r\n     VALUES (ORIGINAL_LOGIN(), GETDATE());\r\nEND;\r\nGO\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/03\/login-trigger-audit-table.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/03\/login-trigger-audit-table-300x144.jpg\" alt=\"\" title=\"login-trigger-audit-table\" width=\"300\" height=\"144\" class=\"alignright size-medium wp-image-1815\" srcset=\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/03\/login-trigger-audit-table-300x144.jpg 300w, https:\/\/craftydba.com\/wp-content\/uploads\/2012\/03\/login-trigger-audit-table.jpg 692w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><br \/>\nThe screen shot on the right shows records in the Log Database Logins table for my user id as well as the Red Gate toolset.<\/p>\n<p>&nbsp;<\/p>\n<p>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 <span style=\"color: #ff0000;\">sa<\/span> account. Please note, this is different than NT USERS who are part of the <span style=\"color: #ff0000;\">sysadmins<\/span> 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.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"tsql - stop all users during maintenance period\">\r\n--\r\n-- Prevent user logins during maintenance \r\n--\r\n\r\n-- Remove existing logon trigger\r\nIF  EXISTS (SELECT * FROM master.sys.server_triggers \r\n    WHERE parent_class_desc = 'SERVER' AND name = N'DISALLOW_USERS')\r\nDROP TRIGGER [DISALLOW_USERS] ON ALL SERVER\r\nGO\r\n\r\n-- Create new logon trigger\r\nCREATE TRIGGER DISALLOW_USERS\r\nON ALL SERVER FOR LOGON\r\nAS\r\nBEGIN\r\n\r\n    -- Perform maintenance between 11 PM and 7 AM\r\n    IF ( (ORIGINAL_LOGIN() &lt;&gt; 'sa') AND (DATEPART(HOUR, GETDATE()) IN (23, 0, 1, 2, 3, 4, 5, 6)) )\r\n        ROLLBACK;\r\n\r\nEND;\r\nGO\r\n<\/pre>\n<p>\u00a0<br \/>\n<a href=\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/03\/login-trigger-time-reject.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/03\/login-trigger-time-reject-300x91.jpg\" alt=\"\" title=\"login-trigger-time-reject\" width=\"300\" height=\"91\" class=\"alignright size-medium wp-image-1821\" srcset=\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/03\/login-trigger-time-reject-300x91.jpg 300w, https:\/\/craftydba.com\/wp-content\/uploads\/2012\/03\/login-trigger-time-reject.jpg 624w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><br \/>\nThe screen shot on the right shows my user account getting rejected due to time of day.<\/p>\n<p>&nbsp;<\/p>\n<p>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.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"tsql - stop a particular id from login\">\r\n--\r\n-- Prevent application id login\r\n--\r\n\r\n-- Remove existing logon trigger\r\nIF  EXISTS (SELECT * FROM master.sys.server_triggers \r\n    WHERE parent_class_desc = 'SERVER' AND name = N'DISALLOW_APPID')\r\nDROP TRIGGER [DISALLOW_APPID] ON ALL SERVER\r\nGO\r\n\r\n-- Create new logon trigger\r\nCREATE TRIGGER DISALLOW_APPID\r\nON ALL SERVER FOR LOGON\r\nAS\r\nBEGIN\r\n\r\n    -- STOP FORD_USER - FROM OBJECT OWNERSHIP TALK\r\n    IF (ORIGINAL_LOGIN() = 'FORD_USER') \r\n        ROLLBACK;\r\n\r\nEND;\r\nGO\r\n<\/pre>\n<p><a href=\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/03\/login-trigger-appid-reject.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/03\/login-trigger-appid-reject-300x90.jpg\" alt=\"\" title=\"login-trigger-appid-reject\" width=\"300\" height=\"90\" class=\"alignright size-medium wp-image-1826\" srcset=\"https:\/\/craftydba.com\/wp-content\/uploads\/2012\/03\/login-trigger-appid-reject-300x90.jpg 300w, https:\/\/craftydba.com\/wp-content\/uploads\/2012\/03\/login-trigger-appid-reject.jpg 620w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><br \/>\nThe screen shot on the right shows the FORD_USER account getting rejected because of its name.<\/p>\n<p>&nbsp;<\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &#8211; TSQL code to be executed after an action or INSTEAD OF &#8211; TSQL code to be executed in lue of a action. Triggers are commonly used in following tasks: Auditing data changes by place&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[396,398,317,394,391,400,395,80,399,12,397,15,393,392,28,401,226,390,29],"class_list":["post-1799","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-after","tag-alter-trigger","tag-create-trigger","tag-database","tag-ddl","tag-disable-trigger","tag-dml","tag-drop-trigger","tag-enable-trigger","tag-free-code","tag-instead-of","tag-john-f-miner-iii","tag-login","tag-server","tag-sql-server","tag-sys-server_triggers","tag-table","tag-triggers","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1799","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1799"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1799\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1799"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1799"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1799"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}