{"id":3249,"date":"2022-04-14T08:31:33","date_gmt":"2022-04-14T01:31:33","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3249"},"modified":"2022-04-14T08:32:06","modified_gmt":"2022-04-14T01:32:06","slug":"sql-server-contained-databases","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-administration\/sql-server-contained-databases\/","title":{"rendered":"SQL Server Contained Databases"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn about SQL Server contained databases and how to create a contained database using T-SQL.<\/p>\n\n\n\n

Introduction to the SQL Server contained database #<\/a><\/h2>\n\n\n\n

A contained database is a self-contained database that has its own metadata, database settings, and configurations and does not depend on the SQL Server instance that hosts it.<\/p>\n\n\n\n

By using a contained database, you can move it to another server without creating any security issues as well as orphan SQL logins.<\/p>\n\n\n\n

To create a user<\/a> to a regular database, you need to first create a login<\/a> that connects to the master<\/code> database:<\/p>\n\n\n

CREATE<\/span> LOGIN login_name \nWITH<\/span> PASSWORD<\/span> = 'strong_password'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
And then create a user that connects to the user<\/code> database:<\/p>\n\n\n
CREATE<\/span> USER<\/span> 'user_name'<\/span> \nFOR<\/span> LOGIN 'login_name'<\/span>;\t<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want to change the password of the user, you need to change the login’s password:<\/p>\n\n\n
ALTER<\/span> LOGIN login_name \nWITH<\/span> PASSWORD<\/span> = 'strong_password'<\/span>;\t<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
When you move the database to another SQL Server instance, you need to create the same login_name<\/code> from the current SQL Server instance in the new SQL Server instance and map the user with the newly created login.<\/p>\n\n\n\n
However, if you use a contained database, you can directly create a user that connects to the user database:<\/p>\n\n\n
CREATE<\/span> USER<\/span> user_name \nWITH<\/span> PASSWORD<\/span> = 'strong_password'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
When you move the database to another SQL Server instance, you need to use the same user_name<\/code> to connect to the database.<\/p>\n\n\n\n
To change the password, you can directly change the user’s password like this:<\/p>\n\n\n
ALTER<\/span> USER<\/span> user_name \nWITH<\/span> PASSWORD<\/span> = 'strong_password'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Creating a contained database #<\/a><\/h2>\n\n\n\n
First, enable the containment feature at the SQL Server instance level using the sp_configure<\/code> stored procedure:<\/p>\n\n\n
USE<\/span> master<\/span>\nGO<\/span>\n\nsp_configure 'show advanced options'<\/span>, 1<\/span>\nGO<\/span>\nRECONFIGURE\nGO<\/span>\n\nsp_configure 'CONTAINED DATABASE AUTHENTICATION'<\/span>, 1<\/span>\nGO<\/span>\nRECONFIGURE\nGO<\/span>\n\nsp_configure 'show advanced options'<\/span>, 0<\/span> \nGO<\/span>\nRECONFIGURE\nGO<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> DATABASE<\/span> CRM\n\tCONTAINMENT = PARTIAL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To verify if a database is a contained database, you can use the sys.databases<\/code> view. The following statement checks if the CRM<\/code> database is a contained database:<\/p>\n\n\n
SELECT<\/span>  \n  name<\/span>,\n  containment\nFROM<\/span> sys.databases\nWHERE<\/span> name<\/span> = 'CRM'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Third, create a user that connects to the CRM contained database:<\/p>\n\n\n
USE<\/span> CRM;\n\nCREATE<\/span> USER<\/span> bob\nWITH<\/span> PASSWORD<\/span> = 'UixoJN72.'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To list all users of a contained database, you use the sys.database_principals<\/code> view:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  type_desc,\n  authentication_type_desc\nFROM<\/span> sys.database_principals\nWHERE<\/span> authentication_type = 2<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Connect to the contained database using SSMS #<\/a><\/h2>\n\n\n\n
First, enter the server name, user, and password and click the Option >> button:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Second, enter the contained database that you want to connect in the connection properties tab and click the Connect button:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
If you connect to the CRM contained database successfully, you’ll see that the user bob only can see the CRM database:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Converting a regular database to a contained database #<\/a><\/h2>\n\n\n\n
First, create a database inventory with a login cat and a user bobcat for the demonstration:<\/p>\n\n\n
CREATE<\/span> DATABASE<\/span> inventory;\n\nCREATE<\/span> LOGIN cat\nWITH<\/span> PASSWORD<\/span> = 'AhudnmX9!'<\/span>;\n\nUSE<\/span> inventory;\n\nCREATE<\/span> USER<\/span> bobcat\nFOR<\/span> LOGIN cat;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, convert the inventory database to a contained database using the ALTER DATABASE statement:<\/p>\n\n\n
ALTER<\/span> DATABASE<\/span> inventory \nSET<\/span> CONTAINMENT = PARTIAL<\/span> WITH<\/span> NO_WAIT;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
EXEC sp_migrate_user_to_contained \n    @username = N'bobcat' ,   \n    @rename<\/span> = N'keep_name'<\/span>,   \n    @disablelogin =N'disable_login'<\/span>; <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
 This stored procedure call converts the database user bobcat to a contained database user. It keeps the same username. <\/p>\n\n\n\n
If you want to change to copy the login name to the contained database user, you can pass the N'copy_login_name'<\/code> to the @rename<\/code> parameter.<\/p>\n\n\n\n
The N'disable_login'<\/code> argument instructs the stored procedure to disable the login bob to the master database. If you don’t want to disable the login, you can use the N'do_not_disable_login'<\/code> argument.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
  • A contained database is a self-contained database that has its own metadata, database settings, and configurations and does not depend on the SQL Server instance.<\/li><\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Third, convert a database user bobcat, which is mapped to a SQL Server login cat, to a contained database user with a password using the sp_migrate_user_to_contained<\/a><\/code> system stored procedure:<\/p>\n\n\n
Second, create a contained database using the CREATE DATABASE<\/a><\/code> statement with the containment set to partial:<\/p>\n\n\n