{"id":825,"date":"2013-08-23T12:10:18","date_gmt":"2013-08-23T19:10:18","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=825"},"modified":"2013-10-22T14:16:39","modified_gmt":"2013-10-22T21:16:39","slug":"how-to-avoid-orphaned-database-users-with-sql-server-authentication","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/","title":{"rendered":"How To Avoid Orphaned Database Users with SQL Server Authentication"},"content":{"rendered":"<p>One common issue that database administrators often run into is the old, familiar \u201corphaned\u201d user problem. This happens when you use SQL Server Authentication to create a SQL Server login on your database server. When you do this, SQL Server generates a unique SID for that SQL Server login. After you create the SQL Server login, you typically create a database user in a user database on that server instance, and associate the database user with that SQL Server login.<\/p>\n<p>This works fine until you try to restore that user database to another SQL Server instance. If you previously created a SQL Server login with the same UserID on the new server, the SID for that SQL Server login will not match the database user in the user database that you have restored (from the other database instance). Hence the term \u201corphaned\u201d user.\u00a0 This is an especially big issue if you are using database mirroring, since your database users will be orphans when you failover from one instance to the other instance. It is also an issue with log shipping, and it often comes up when you migrate from an old database server to a new database server.<\/p>\n<p>There are several ways to fix this, but the best thing (outside of just using Windows Authentication and avoiding the problem), is to create the new server login using the same SID as on the original server. Just like you see below:<\/p>\n<pre class=\"csharpcode\"><span class=\"rem\">-- Get Sids for all SQL Server logins on the old server instance<\/span>\r\n<span class=\"kwrd\">SELECT<\/span> name, [sid] \r\n<span class=\"kwrd\">FROM<\/span> sys.server_principals\r\n<span class=\"kwrd\">WHERE<\/span> [type] = <span class=\"str\">'s'<\/span>; \r\n\r\n<span class=\"rem\">-- Create new SQL Login on new server instance<\/span>\r\n<span class=\"kwrd\">IF<\/span>  <span class=\"kwrd\">EXISTS<\/span> (<span class=\"kwrd\">SELECT<\/span> * <span class=\"kwrd\">FROM<\/span> sys.server_principals <span class=\"kwrd\">WHERE<\/span> name = N<span class=\"str\">'SQLAppUser'<\/span>)\r\n    <span class=\"kwrd\">DROP<\/span> LOGIN SQLAppUser;\r\n<span class=\"kwrd\">GO<\/span>\r\n\r\n<span class=\"rem\">-- Use the sid from the old server instance <\/span>\r\n<span class=\"kwrd\">CREATE<\/span> LOGIN SQLAppUser <span class=\"kwrd\">WITH<\/span> PASSWORD = N<span class=\"str\">'YourStrongPassword#'<\/span>, sid = 0x2F5B769F543973419BCEF78DE9FC1A64,\r\nDEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=<span class=\"kwrd\">OFF<\/span>, CHECK_POLICY=<span class=\"kwrd\">OFF<\/span>;\r\n<span class=\"kwrd\">GO<\/span><\/pre>\n<style type=\"text\/css\"><!--\n.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }\n--><\/style>\n","protected":false},"excerpt":{"rendered":"<p>One common issue that database administrators often run into is the old, familiar \u201corphaned\u201d user problem. This happens when you use SQL Server Authentication to create a SQL Server login on your database server. When you do this, SQL Server generates a unique SID for that SQL Server login. After you create the SQL Server [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,29,30,31,156],"tags":[178,189],"class_list":["post-825","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005","category-sql-server-2008","category-sql-server-2008-r2","category-sql-server-2012","category-sql-server-2014","tag-orphaned-database-users","tag-sql-server-authentication"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How To Avoid Orphaned Database Users with SQL Server Authentication - Glenn Berry<\/title>\n<meta name=\"description\" content=\"Describes how to avoid orphaned database users with SQL Server authentication and why it is important\" \/>\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.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Avoid Orphaned Database Users with SQL Server Authentication - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"Describes how to avoid orphaned database users with SQL Server authentication and why it is important\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2013-08-23T19:10:18+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-10-22T21:16:39+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\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\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/\",\"name\":\"How To Avoid Orphaned Database Users with SQL Server Authentication - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2013-08-23T19:10:18+00:00\",\"dateModified\":\"2013-10-22T21:16:39+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"description\":\"Describes how to avoid orphaned database users with SQL Server authentication and why it is important\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Avoid Orphaned Database Users with SQL Server Authentication\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How To Avoid Orphaned Database Users with SQL Server Authentication - Glenn Berry","description":"Describes how to avoid orphaned database users with SQL Server authentication and why it is important","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.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/","og_locale":"en_US","og_type":"article","og_title":"How To Avoid Orphaned Database Users with SQL Server Authentication - Glenn Berry","og_description":"Describes how to avoid orphaned database users with SQL Server authentication and why it is important","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/","og_site_name":"Glenn Berry","article_published_time":"2013-08-23T19:10:18+00:00","article_modified_time":"2013-10-22T21:16:39+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/","name":"How To Avoid Orphaned Database Users with SQL Server Authentication - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2013-08-23T19:10:18+00:00","dateModified":"2013-10-22T21:16:39+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"description":"Describes how to avoid orphaned database users with SQL Server authentication and why it is important","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-avoid-orphaned-database-users-with-sql-server-authentication\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"How To Avoid Orphaned Database Users with SQL Server Authentication"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/825","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=825"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/825\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=825"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=825"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=825"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}