{"id":721,"date":"2014-04-03T13:13:32","date_gmt":"2014-04-03T17:13:32","guid":{"rendered":"http:\/\/binary-world.com\/blogs\/?p=721"},"modified":"2015-12-01T16:37:38","modified_gmt":"2015-12-01T21:37:38","slug":"how-to-login-sql-server-after-all-admin-accounts-removed-locked","status":"publish","type":"post","link":"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/","title":{"rendered":"How to login to sql server after all admin accounts removed and you are locked"},"content":{"rendered":"<p>Recently I had an interesting situation. Consider below situation<\/p>\n<ul>\n<li>You want to <em>connect to sql server as sysadmin<\/em> but you are not added to sql server logins using windows authentication<\/li>\n<li><strong>BUILTIN\\Administrators<\/strong> group has been<strong> removed from SQL Server<\/strong><\/li>\n<li>You have <strong>no way to login to sql server<\/strong> because all <strong>admin accounts have been removed<\/strong> or deleted<\/li>\n<\/ul>\n<p>When you face <em>total sql server lockout<\/em> situation in sql server perform the following steps.<\/p>\n<p>1. Stop <strong>SQL Server<\/strong> Service and <strong>SQL Agent<\/strong> service<\/p>\n<p>2. Open a cmd prompt window (Start -&gt; Run -&gt; type <strong>cmd<\/strong> and press the &#8216;OK&#8217; button) and navigate to <strong>SQL Server&#8217;s Binn directory<\/strong> as shown below.<\/p>\n<div>\n<dl id=\"attachment_755\">\n<dt><a href=\"http:\/\/binary-world.com\/blogs\/wp-content\/uploads\/2014\/04\/sql-server-account-locked-start-single-user-mode.png\"><img loading=\"lazy\" decoding=\"async\" alt=\"Login to sql server after BUILTIN\\Administrator group deleted and account locked\" src=\"http:\/\/binary-world.com\/blogs\/wp-content\/uploads\/2014\/04\/sql-server-account-locked-start-single-user-mode.png\" width=\"535\" height=\"64\" \/><\/a><\/dt>\n<dd>Starting sql server in Single User mode<\/dd>\n<\/dl>\n<\/div>\n<p>3. Once you are in <strong>SQL Server&#8217;s Binn<\/strong> directory type <em>&#8216;sqlservr -m&#8217;<\/em> command to start SQL Server in single user mode.<\/p>\n<pre>sqlservr.exe -m<\/pre>\n<p>4. Once <strong>SQL Server<\/strong> is started in <strong>single user mode<\/strong> open another command prompt and type below command.<\/p>\n<pre>sqlcmd -E<\/pre>\n<p>Note that I used -E switch for trusted mode and didn&#8217;t specify any server name so it will by default connect to localhost. If you have named instance then use -S for servername, -U for userid and -P for password. For more help type <em>sqlcmd \/?<\/em><\/p>\n<p>5. Once you are connected to sql server using sqlcmd you can add yourself back to sql login and then make yourself sysadmin to take full control of the instance.<\/p>\n<pre>&gt; CREATE LOGIN [MyDomain\\SomeUserName] FROM WINDOWS\r\n&gt; GO<\/pre>\n<p>If you have Mixed Mode enabled then you may try something like below too<\/p>\n<pre>&gt; CREATE LOGIN TempUser1 WITH PASSWORD = &#039;MyPassWord@1234&#039;\r\n&gt; GO<\/pre>\n<p>6. Once login is created you have to add that login to <em>sysadmin<\/em> role using below command.<\/p>\n<pre>&gt; exec sp_addsrvrolemember [MyDomain\\SomeUserName],[sysadmin]\r\n&gt; GO<\/pre>\n<p>or<\/p>\n<pre>&gt; exec sp_addsrvrolemember [TempUser1],[sysadmin]\r\n&gt; GO<\/pre>\n<p>7. Go back to our first window where we launched sqlserver using -m switch. Type Ctrl+C and it will ask you stop sqlserver. <strong>Just type Y<\/strong><\/p>\n<p>8. <strong>Restart SQL Server<\/strong> from Services and try to connect using SSMS&#8230;&#8230; That&#8217;s it. You should be able to connect to sql server now<\/p>\n<p>Hope this will save you some time and make you feel home.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Recently I had an interesting situation. Consider below situation You want to connect to sql server as sysadmin but you are not added to sql server logins using windows authentication BUILTIN\\Administrators group has been removed from SQL Server You have &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"more-link\" href=\"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/\"> <span class=\"screen-reader-text\">How to login to sql server after all admin accounts removed and you are locked<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":606,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[117,27],"tags":[121,158],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\r\n<title>How to login to sql server after all admin accounts removed and you are locked - BinaryWorld Blog<\/title>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to login to sql server after all admin accounts removed and you are locked - BinaryWorld Blog\" \/>\r\n<meta property=\"og:description\" content=\"Recently I had an interesting situation. Consider below situation You want to connect to sql server as sysadmin but you are not added to sql server logins using windows authentication BUILTINAdministrators group has been removed from SQL Server You have &hellip; How to login to sql server after all admin accounts removed and you are locked Read More &raquo;\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/\" \/>\r\n<meta property=\"og:site_name\" content=\"BinaryWorld Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2014-04-03T17:13:32+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2015-12-01T21:37:38+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg\" \/>\r\n\t<meta property=\"og:image:width\" content=\"150\" \/>\r\n\t<meta property=\"og:image:height\" content=\"150\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\r\n<meta name=\"author\" content=\"Binary World\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Binary World\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/\",\"url\":\"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/\",\"name\":\"How to login to sql server after all admin accounts removed and you are locked - BinaryWorld Blog\",\"isPartOf\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg\",\"datePublished\":\"2014-04-03T17:13:32+00:00\",\"dateModified\":\"2015-12-01T21:37:38+00:00\",\"author\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0\"},\"breadcrumb\":{\"@id\":\"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/#primaryimage\",\"url\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg\",\"contentUrl\":\"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg\",\"width\":150,\"height\":150,\"caption\":\"T SQL Date format convert function\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/binaryworld.net\/blogs\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to login to sql server after all admin accounts removed and you are locked\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#website\",\"url\":\"https:\/\/binaryworld.net\/blogs\/\",\"name\":\"BinaryWorld Blog\",\"description\":\"Tips and Tutorials for Microsoft SQL Server, SSIS, SSAS, Business Intelligence, C#, .net\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/binaryworld.net\/blogs\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0\",\"name\":\"Binary World\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g\",\"caption\":\"Binary World\"},\"description\":\"Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.\",\"url\":\"https:\/\/binaryworld.net\/blogs\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to login to sql server after all admin accounts removed and you are locked - BinaryWorld Blog","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:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/","og_locale":"en_US","og_type":"article","og_title":"How to login to sql server after all admin accounts removed and you are locked - BinaryWorld Blog","og_description":"Recently I had an interesting situation. Consider below situation You want to connect to sql server as sysadmin but you are not added to sql server logins using windows authentication BUILTINAdministrators group has been removed from SQL Server You have &hellip; How to login to sql server after all admin accounts removed and you are locked Read More &raquo;","og_url":"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/","og_site_name":"BinaryWorld Blog","article_published_time":"2014-04-03T17:13:32+00:00","article_modified_time":"2015-12-01T21:37:38+00:00","og_image":[{"width":150,"height":150,"url":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg","type":"image\/jpeg"}],"author":"Binary World","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Binary World","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/","url":"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/","name":"How to login to sql server after all admin accounts removed and you are locked - BinaryWorld Blog","isPartOf":{"@id":"https:\/\/binaryworld.net\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/#primaryimage"},"image":{"@id":"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/#primaryimage"},"thumbnailUrl":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg","datePublished":"2014-04-03T17:13:32+00:00","dateModified":"2015-12-01T21:37:38+00:00","author":{"@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0"},"breadcrumb":{"@id":"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/#primaryimage","url":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg","contentUrl":"https:\/\/binaryworld.net\/blogs\/wp-content\/uploads\/2014\/02\/sql-server-logo-e1397590261883.jpg","width":150,"height":150,"caption":"T SQL Date format convert function"},{"@type":"BreadcrumbList","@id":"https:\/\/binaryworld.net\/blogs\/how-to-login-sql-server-after-all-admin-accounts-removed-locked\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/binaryworld.net\/blogs\/"},{"@type":"ListItem","position":2,"name":"How to login to sql server after all admin accounts removed and you are locked"}]},{"@type":"WebSite","@id":"https:\/\/binaryworld.net\/blogs\/#website","url":"https:\/\/binaryworld.net\/blogs\/","name":"BinaryWorld Blog","description":"Tips and Tutorials for Microsoft SQL Server, SSIS, SSAS, Business Intelligence, C#, .net","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/binaryworld.net\/blogs\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/77cf0a9a512dd22bff93c6a1b6374fe0","name":"Binary World","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/binaryworld.net\/blogs\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eaea47799daa577835eb53e64dfd3e13?s=96&d=mm&r=g","caption":"Binary World"},"description":"Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.","url":"https:\/\/binaryworld.net\/blogs\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts\/721"}],"collection":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/comments?post=721"}],"version-history":[{"count":0,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/posts\/721\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/media\/606"}],"wp:attachment":[{"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/media?parent=721"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/categories?post=721"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/binaryworld.net\/blogs\/wp-json\/wp\/v2\/tags?post=721"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}