{"id":9201,"date":"2019-02-06T14:17:11","date_gmt":"2019-02-06T14:17:11","guid":{"rendered":"https:\/\/dbtut.com\/?p=9201"},"modified":"2019-02-07T05:24:28","modified_gmt":"2019-02-07T05:24:28","slug":"sql-server-logon-trigger","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/","title":{"rendered":"SQL Server Logon Trigger"},"content":{"rendered":"

Hello there,
\nUser management is very important in our database systems. We prefer DBAs to create or use more windows authentication instead of sql authentication. But conditions may not always be what we want. Today, many applications use sql authentication to access the database. These SQL accounts are easily accessed via SSMS (SQL Server Management Studio), and anyone with sql user information will have access to the database. To avoid this, we will write a trigger.<\/p>\n

We will block database access through Management Studio with the user accounts we set in this trigger. Users will receive an error message via Management Studio when they try to login with these accounts.<\/p>\n

CREATE TRIGGER [DISABLE_LOGON_APP]\nON ALL SERVER \nFOR LOGON\nAS\nBEGIN\n\n   DECLARE @program_name nvarchar(128)\n   DECLARE @host_name nvarchar(128)\n\n   SELECT @program_name = program_name, \n      @host_name = host_name\n   FROM sys.dm_exec_sessions AS c\n   WHERE c.session_id = @@spid\n\n\n   IF ORIGINAL_LOGIN() IN('sqluser1','sqluser2','testuser') \n      AND @program_name LIKE '%Management%Studio%' \n   BEGIN\n      RAISERROR('This login is for application use only.',16,1)\n      ROLLBACK;\n   END<\/pre>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
Hello there, User management is very important in our database systems. We prefer DBAs to create or use more windows authentication instead of sql authentication. But conditions may not always be what we want. Today, many applications use sql authentication to access the database. These SQL accounts are easily accessed via SSMS (SQL Server Management …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":398,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[1939,1938,1936,1940,1550,653],"class_list":["post-9201","post","type-post","status-publish","format-standard","","category-mssql","tag-logon","tag-logontrigger","tag-mssql","tag-security","tag-sql","tag-trigger"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":179,"today_views":0},"yoast_head":"\nSQL Server Logon Trigger - Database Tutorials<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Logon Trigger - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"Hello there, User management is very important in our database systems. We prefer DBAs to create or use more windows authentication instead of sql authentication. But conditions may not always be what we want. Today, many applications use sql authentication to access the database. These SQL accounts are easily accessed via SSMS (SQL Server Management …\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-02-06T14:17:11+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-02-07T05:24:28+00:00\" \/>\n<meta name=\"author\" content=\"S\u00fcleyman Ka\u015f\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"S\u00fcleyman Ka\u015f\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/\"},\"author\":{\"name\":\"S\u00fcleyman Ka\u015f\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/590daafb086b3b7a2da2b24721b4c567\"},\"headline\":\"SQL Server Logon Trigger\",\"datePublished\":\"2019-02-06T14:17:11+00:00\",\"dateModified\":\"2019-02-07T05:24:28+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/\"},\"wordCount\":114,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"keywords\":[\"logon\",\"logontrigger\",\"mssql\",\"security\",\"sql\",\"Trigger\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/\",\"name\":\"SQL Server Logon Trigger - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"datePublished\":\"2019-02-06T14:17:11+00:00\",\"dateModified\":\"2019-02-07T05:24:28+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Logon Trigger\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/590daafb086b3b7a2da2b24721b4c567\",\"name\":\"S\u00fcleyman Ka\u015f\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b09c3a506e893211d4d099ffc9a94bff3eebcac8bcad74c26ccdafe9f15df03?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b09c3a506e893211d4d099ffc9a94bff3eebcac8bcad74c26ccdafe9f15df03?s=96&d=mm&r=g\",\"caption\":\"S\u00fcleyman Ka\u015f\"},\"description\":\"I have been managing MS SQL database for 3 years. And I have been managing HANA DB for the last 1 years. I'm Microsoft Certified Professional. My competencies; - Database Administrator, - Database Management, - Database Technologies, - SAP BASIS, - System Management, - Data Analysis and Reporting\",\"sameAs\":[\"http:\/\/www.suleymankas.com\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/suleymankas\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Logon Trigger - Database Tutorials","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Logon Trigger - Database Tutorials","og_description":"Hello there, User management is very important in our database systems. We prefer DBAs to create or use more windows authentication instead of sql authentication. But conditions may not always be what we want. Today, many applications use sql authentication to access the database. These SQL accounts are easily accessed via SSMS (SQL Server Management …","og_url":"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/","og_site_name":"Database Tutorials","article_published_time":"2019-02-06T14:17:11+00:00","article_modified_time":"2019-02-07T05:24:28+00:00","author":"S\u00fcleyman Ka\u015f","twitter_card":"summary_large_image","twitter_misc":{"Written by":"S\u00fcleyman Ka\u015f","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/"},"author":{"name":"S\u00fcleyman Ka\u015f","@id":"https:\/\/dbtut.com\/#\/schema\/person\/590daafb086b3b7a2da2b24721b4c567"},"headline":"SQL Server Logon Trigger","datePublished":"2019-02-06T14:17:11+00:00","dateModified":"2019-02-07T05:24:28+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/"},"wordCount":114,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"keywords":["logon","logontrigger","mssql","security","sql","Trigger"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/","url":"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/","name":"SQL Server Logon Trigger - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"datePublished":"2019-02-06T14:17:11+00:00","dateModified":"2019-02-07T05:24:28+00:00","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/06\/sql-server-logon-trigger\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"SQL Server Logon Trigger"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/590daafb086b3b7a2da2b24721b4c567","name":"S\u00fcleyman Ka\u015f","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b09c3a506e893211d4d099ffc9a94bff3eebcac8bcad74c26ccdafe9f15df03?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b09c3a506e893211d4d099ffc9a94bff3eebcac8bcad74c26ccdafe9f15df03?s=96&d=mm&r=g","caption":"S\u00fcleyman Ka\u015f"},"description":"I have been managing MS SQL database for 3 years. And I have been managing HANA DB for the last 1 years. I'm Microsoft Certified Professional. My competencies; - Database Administrator, - Database Management, - Database Technologies, - SAP BASIS, - System Management, - Data Analysis and Reporting","sameAs":["http:\/\/www.suleymankas.com"],"url":"https:\/\/dbtut.com\/index.php\/author\/suleymankas\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/9201","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/398"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=9201"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/9201\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=9201"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=9201"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=9201"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}