{"id":16079,"date":"2021-04-01T13:45:36","date_gmt":"2021-04-01T11:45:36","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/"},"modified":"2021-04-01T13:45:36","modified_gmt":"2021-04-01T11:45:36","slug":"sql-server-contained-database","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/","title":{"rendered":"SQL Server &#8211; Contained Database"},"content":{"rendered":"<h2>Introduction<\/h2>\n<p>You may have encounter already case when an application offers the possibility to create and maintain SQL database users and consequently the associated SQL Server instance logins. That&#8217;s of course not especially really what a DBA likes, loosing control on the logins that are created on the instance he is responsible of. But sometimes you have to deal with such situation especially when a customer ask you to enable this.<br \/>\nWell, then it&#8217;s starts to be more complicated when the application of that database is part of an always on availability group. You can imagine what happen in case of failover, all the logins created within the application will not be present on the secondary replicas and all connections to the new primary replica will fail.<\/p>\n<p>We had a sync process in place to make sure that the logins were available in all replicas. That&#8217;s a good solution, but the users could also change their password through the application. In such case the process to sync the logins becomes more complicated. Thus before starting a long development process, I wanted to experiment and test the containment option that presumably could be a good option in such case.<\/p>\n<p>In fact, experimenting how containment option is working, the creation of the database users must be done differently and therefore if your application is not aware or fitting with this option it won&#8217;t solve your problem.<\/p>\n<h2>Set the Containment option for your database<\/h2>\n<p>The first thing you have to do is to configure your SQL Server instance to enable the containment authentication.<\/p>\n<p>For that you have to change the &#8220;contained database authentication&#8221; configuration option.<\/p>\n<p>&nbsp;<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">--###Scripts:\n---------------------------------------------------------------------\nexecute sp_configure 'contained database authentication',1\nReconfigure\n---------------------------------------------------------------------<\/pre>\n<p>Caution: if you are in an always on availability group situation, of course do not forget to do the same on all the replicas otherwise you will face some problems after a primary switch.<\/p>\n<p>Then you have to change your data base to enable this option.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">--###Script:\n---------------------------------------------------------------------\nUSE [master]\nGO\nALTER DATABASE [DB_Contained] SET CONTAINMENT = PARTIAL WITH NO_WAIT\nGO\n---------------------------------------------------------------------<\/pre>\n<p>You can do the same using your database properties GUI in SSMS.<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/chc_blog_20210401_1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-49082\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/chc_blog_20210401_1.png\" alt=\"\" width=\"300\" height=\"271\" \/><\/a><\/p>\n<h2>Create the user on your database level<\/h2>\n<p>Now that the option is enable, if you want to create a new database user, you will see that new options appeared &#8220;SQL User with password&#8221;<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/chc_blog_20210401_2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-49083\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/chc_blog_20210401_2.png\" alt=\"\" width=\"300\" height=\"274\" \/><\/a><\/p>\n<p>You can create you database user using the following script<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">--###Script:\n-----------------------------------------------------------\nUSE [DB_Contained]\nGO\nCREATE USER [user_with_pwd] WITH PASSWORD=N'**********'\nGO\n-----------------------------------------------------------<\/pre>\n<p>You can also create a domain account<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">--###Script:\n-----------------------------------------------------------\nUSE [DB_Contained]\nGO\nCREATE USER [YourDomain\\YourAccount] WITHOUT LOGIN\nGO\n-----------------------------------------------------------<\/pre>\n<p>Be aware that in this case the database user will be shown with an associated login but it will not be present at instance level and you will not see it listed in the server logins.<\/p>\n<h2>Connect to you database<\/h2>\n<p>Remember that with the contained database the login will be done on database level therefore you must specify the catalog (the database) to which you want to establish the connection otherwise the connection will failed.<\/p>\n<h2>Conclusion<\/h2>\n<p>This could be a nice solution to have a portable database and make it independent from the instance it is located on, and therefore without having to worry about to copy the logins when it is moved to another server.<br \/>\nBut of course it must fit your case.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction You may have encounter already case when an application offers the possibility to create and maintain SQL database users and consequently the associated SQL Server instance logins. That&#8217;s of course not especially really what a DBA likes, loosing control on the logins that are created on the instance he is responsible of. But sometimes [&hellip;]<\/p>\n","protected":false},"author":38,"featured_media":16080,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[],"type_dbi":[],"class_list":["post-16079","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Server - Contained Database - dbi Blog<\/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:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server - Contained Database\" \/>\n<meta property=\"og:description\" content=\"Introduction You may have encounter already case when an application offers the possibility to create and maintain SQL database users and consequently the associated SQL Server instance logins. That&#8217;s of course not especially really what a DBA likes, loosing control on the logins that are created on the instance he is responsible of. But sometimes [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-04-01T11:45:36+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/chc_blog_20210401_1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"690\" \/>\n\t<meta property=\"og:image:height\" content=\"623\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Christophe Cosme\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Christophe Cosme\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 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-contained-database\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/\"},\"author\":{\"name\":\"Christophe Cosme\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a\"},\"headline\":\"SQL Server &#8211; Contained Database\",\"datePublished\":\"2021-04-01T11:45:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/\"},\"wordCount\":514,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/chc_blog_20210401_1.png\",\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/\",\"name\":\"SQL Server - Contained Database - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/chc_blog_20210401_1.png\",\"datePublished\":\"2021-04-01T11:45:36+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/chc_blog_20210401_1.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/chc_blog_20210401_1.png\",\"width\":690,\"height\":623},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server &#8211; Contained Database\"}]},{\"@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\/5cbf7c261d165a45977c85f535d6cb6a\",\"name\":\"Christophe Cosme\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g\",\"caption\":\"Christophe Cosme\"},\"description\":\"Christophe Cosme has more than 22 years of experience in architecture, development and implementation of IT solutions including 17 years in the Business Intelligence area as well as 10 years of experience working with the Microsoft SQL Server Business Intelligence suite. His experience and qualifications cover project management, business analysis, functional and technical requirements. His expertise also includes the implementation of IT solutions in national as well as international organizations. Christophe Cosme is Expert in MS SQL Server DB Engine, SSIS, SSAS, SSRS and PowerBI. Prior to joining dbi services, Christophe Cosme was Solution Lead Business Intelligence Diabetes Care at Hoffmann-La-Roche Ltd in Basel . He also worked as Senior Manager Head of Business Intelligence for Swiss International Airlines Ltd. Christophe Cosme holds a Master's degree in Engineering, Automation from the IT\u2013 ENSISA (F). His branch-related experience covers pharmaceuticals, IT and the airline sector.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/christophe-cosme\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server - Contained Database - dbi 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:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server - Contained Database","og_description":"Introduction You may have encounter already case when an application offers the possibility to create and maintain SQL database users and consequently the associated SQL Server instance logins. That&#8217;s of course not especially really what a DBA likes, loosing control on the logins that are created on the instance he is responsible of. But sometimes [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/","og_site_name":"dbi Blog","article_published_time":"2021-04-01T11:45:36+00:00","og_image":[{"width":690,"height":623,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/chc_blog_20210401_1.png","type":"image\/png"}],"author":"Christophe Cosme","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Christophe Cosme","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/"},"author":{"name":"Christophe Cosme","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a"},"headline":"SQL Server &#8211; Contained Database","datePublished":"2021-04-01T11:45:36+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/"},"wordCount":514,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/chc_blog_20210401_1.png","articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/","name":"SQL Server - Contained Database - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/chc_blog_20210401_1.png","datePublished":"2021-04-01T11:45:36+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/chc_blog_20210401_1.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/chc_blog_20210401_1.png","width":690,"height":623},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-contained-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server &#8211; Contained Database"}]},{"@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\/5cbf7c261d165a45977c85f535d6cb6a","name":"Christophe Cosme","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g","caption":"Christophe Cosme"},"description":"Christophe Cosme has more than 22 years of experience in architecture, development and implementation of IT solutions including 17 years in the Business Intelligence area as well as 10 years of experience working with the Microsoft SQL Server Business Intelligence suite. His experience and qualifications cover project management, business analysis, functional and technical requirements. His expertise also includes the implementation of IT solutions in national as well as international organizations. Christophe Cosme is Expert in MS SQL Server DB Engine, SSIS, SSAS, SSRS and PowerBI. Prior to joining dbi services, Christophe Cosme was Solution Lead Business Intelligence Diabetes Care at Hoffmann-La-Roche Ltd in Basel . He also worked as Senior Manager Head of Business Intelligence for Swiss International Airlines Ltd. Christophe Cosme holds a Master's degree in Engineering, Automation from the IT\u2013 ENSISA (F). His branch-related experience covers pharmaceuticals, IT and the airline sector.","url":"https:\/\/www.dbi-services.com\/blog\/author\/christophe-cosme\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16079","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\/38"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=16079"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16079\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/16080"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=16079"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=16079"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=16079"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=16079"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}