{"id":1909,"date":"2012-04-19T15:30:31","date_gmt":"2012-04-19T15:30:31","guid":{"rendered":"http:\/\/craftydba.com\/?p=1909"},"modified":"2017-10-12T00:47:36","modified_gmt":"2017-10-12T00:47:36","slug":"various-triggers-part-2","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1909","title":{"rendered":"Various Triggers – Part 2"},"content":{"rendered":"

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

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. <\/p>\n

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.<\/p>\n

\r\n--\r\n-- Stop MS Office Users\r\n--\r\n\r\n-- Remove existing logon trigger\r\nIF  EXISTS (SELECT * FROM master.sys.server_triggers \r\nWHERE parent_class_desc = 'SERVER' AND name = N'DISALLOW_MS_OFFICE')\r\n  DROP TRIGGER [DISALLOW_MS_OFFICE] ON ALL SERVER\r\nGO\r\n\r\n\r\n-- Create new logon trigger\r\nCREATE TRIGGER DISALLOW_MS_OFFICE\r\nON ALL SERVER EXECUTE AS 'sa' FOR LOGON\r\nAS\r\nBEGIN\r\n    -- Account for case sensitive collations\r\n    IF LOWER(APP_NAME()) LIKE '%microsoft office system%' \r\n        ROLLBACK;\r\nEND;\r\n<\/pre>\n
 <\/p>\n
The screen shot below shows the SERVER level triggger are enabled.<\/p>\n
\"\"<\/a><\/p>\n
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.<\/p>\n
\"\"<\/a><\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,9],"tags":[12,15,416,417,28,29],"class_list":["post-1909","post","type-post","status-publish","format-standard","hentry","category-db-admin","category-db-dev","tag-free-code","tag-john-f-miner-iii","tag-logon-trigger","tag-revoke-application-by-name","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1909","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=1909"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1909\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1909"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1909"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1909"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}