{"id":7072,"date":"2016-02-05T15:26:07","date_gmt":"2016-02-05T14:26:07","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/"},"modified":"2016-02-05T15:26:07","modified_gmt":"2016-02-05T14:26:07","slug":"sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/","title":{"rendered":"SQL Server Tips: Find sql logins when “windows authentication only” is enabled"},"content":{"rendered":"

A customer asks me to find all sql logins enabled on servers where windows authentication only is enabled.<\/p>\n

The goal is to clean sql logins on all servers through the CMS (central management server). In some cases, sql logins are created even if the authentication is set to \u201cWindows authentication only mode\u201d.<\/p>\n

I think that this query is interesting and I will just share it with you.<\/p>\n

DECLARE @value INT\nUSE [master]\nEXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMicrosoft SQL ServerMSSQLServer', N'LoginMode', @value = @value OUTPUT\nIF @value=1 -- Windows authentication only\nBEGIN\n SELECT name as  FROM sys.sql_logins WHERE is_disabled=0\nEND\nELSE \nPRINT 'mixte mode is used'\n\n<\/pre>\n
Results:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n
Server Name<\/strong><\/td>\nSQL Login drop<\/strong><\/td>\n<\/tr>\n
server1instance1<\/td>\nuser1<\/td>\n<\/tr>\n
server1instance1<\/td>\nuser2<\/td>\n<\/tr>\n
server1instance1<\/td>\nuser3<\/td>\n<\/tr>\n
server2instance2<\/td>\nuser1<\/td>\n<\/tr>\n
server2instance2<\/td>\nuser2<\/td>\n<\/tr>\n
server2instance2<\/td>\nuser3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
You obtain the list of all sql logins that you can delete or disable. I use the clause where is_disabled because in this case (Windows authentication only), the \u2018sa\u2019 account is disable after the installation like the other sql account beginning with \u2018##\u2019 like ##MS_PolicyEventProcessingLogin##.<\/p>\n
In my case, I will drop all sql logins, then I add the Drop login command to my select:<\/p>\n
DECLARE @value INT\nUSE [master]\nEXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMicrosoft SQL ServerMSSQLServer', N'LoginMode', @value = @value OUTPUT\nIF @value=1 --Windows authentication only\nBEGIN\n SELECT 'USE [master];DROP LOGIN ['+name+'];' as [SQL Login drop command] FROM sys.sql_logins WHERE is_disabled = 0\nEND\nELSE PRINT 'mixte mode is enabled'\n\n<\/pre>\n
Results:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n
Server Name<\/strong><\/td>\nSQL Login drop command<\/strong><\/td>\n<\/tr>\n
server1instance1<\/td>\nUSE [master];DROP LOGIN [user1];<\/td>\n<\/tr>\n
server1instance1<\/td>\nUSE [master];DROP LOGIN [user2];<\/td>\n<\/tr>\n
server1instance1<\/td>\nUSE [master];DROP LOGIN [user3];<\/td>\n<\/tr>\n
server2instance2<\/td>\nUSE [master];DROP LOGIN [user1];<\/td>\n<\/tr>\n
server2instance2<\/td>\nUSE [master];DROP LOGIN [user2];<\/td>\n<\/tr>\n
server2instance2<\/td>\nUSE [master];DROP LOGIN [user3];<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Et voila! I can clean all servers… \ud83d\ude0e<\/p>\n
But be careful, this will not delete database-users if you have it linked to your SQL Logins.<\/p>\n","protected":false},"excerpt":{"rendered":"
A customer asks me to find all sql logins enabled on servers where windows authentication only is enabled. The goal is to clean sql logins on all servers through the CMS (central management server). In some cases, sql logins are created even if the authentication is set to \u201cWindows authentication only mode\u201d. I think that […]<\/p>\n","protected":false},"author":15,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[23,49,25,51],"type_dbi":[],"class_list":["post-7072","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-dba","tag-microsoft","tag-security","tag-sql-server"],"acf":[],"yoast_head":"\nSQL Server Tips: Find sql logins when "windows authentication only" is enabled<\/title>\n<meta name=\"description\" content=\"find all sql logins enabled on servers where windows authentication only is enabled\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Tips: Find sql logins when "windows authentication only" is enabled\" \/>\n<meta property=\"og:description\" content=\"find all sql logins enabled on servers where windows authentication only is enabled\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-02-05T14:26:07+00:00\" \/>\n<meta name=\"author\" content=\"St\u00e9phane Haby\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"St\u00e9phane Haby\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/\"},\"author\":{\"name\":\"St\u00e9phane Haby\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\"},\"headline\":\"SQL Server Tips: Find sql logins when “windows authentication only” is enabled\",\"datePublished\":\"2016-02-05T14:26:07+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/\"},\"wordCount\":242,\"commentCount\":0,\"keywords\":[\"DBA\",\"Microsoft\",\"Security\",\"SQL Server\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/\",\"name\":\"SQL Server Tips: Find sql logins when \\\"windows authentication only\\\" is enabled\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-02-05T14:26:07+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\"},\"description\":\"find all sql logins enabled on servers where windows authentication only is enabled\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Tips: Find sql logins when “windows authentication only” is enabled\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\",\"name\":\"St\u00e9phane Haby\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"caption\":\"St\u00e9phane Haby\"},\"description\":\"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals & Pharmaceuticals, Banking \/ Financial Services, and many other industries.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/stephane-haby\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server Tips: Find sql logins when \"windows authentication only\" is enabled","description":"find all sql logins enabled on servers where windows authentication only is enabled","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:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Tips: Find sql logins when \"windows authentication only\" is enabled","og_description":"find all sql logins enabled on servers where windows authentication only is enabled","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/","og_site_name":"dbi Blog","article_published_time":"2016-02-05T14:26:07+00:00","author":"St\u00e9phane Haby","twitter_card":"summary_large_image","twitter_misc":{"Written by":"St\u00e9phane Haby","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/"},"author":{"name":"St\u00e9phane Haby","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"headline":"SQL Server Tips: Find sql logins when “windows authentication only” is enabled","datePublished":"2016-02-05T14:26:07+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/"},"wordCount":242,"commentCount":0,"keywords":["DBA","Microsoft","Security","SQL Server"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/","name":"SQL Server Tips: Find sql logins when \"windows authentication only\" is enabled","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-02-05T14:26:07+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"description":"find all sql logins enabled on servers where windows authentication only is enabled","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-tips-find-sql-logins-when-windows-authentication-only-is-enabled\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server Tips: Find sql logins when “windows authentication only” is enabled"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b","name":"St\u00e9phane Haby","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","caption":"St\u00e9phane Haby"},"description":"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals & Pharmaceuticals, Banking \/ Financial Services, and many other industries.","url":"https:\/\/www.dbi-services.com\/blog\/author\/stephane-haby\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7072","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=7072"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7072\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7072"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7072"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7072"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7072"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}