Contained databases in SQL Server
Contained databases in SQL Server
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolContained databases in SQL Server
January 12, 2016 by Rajendra Gupta As we know there are two types of authentication available in SQL Server Windows authentication and SQL authentication. In Windows authentication we use Active directory authentication to connect with SQL Server which makes the most secure authentication method as it can have complexity, group policy configured at AD level applied to all domain servers while in SQL Authentication SQL users are created inside SQL and provided required permissions. The Permissions includes server wide and database wide. The logins can have certain permissions at the database level might be read or write etc. We need to understand the difference between a user and login here. A login is created at a server level while the user is created at a database level. Each having Unique SID and to work for the user, both SID should be equivalent. Usually, we have the requirement for restoring the staging or UAT databases from production or any new server. When we move or restore the database into a different environment we might come up with issues of Orphan users (where SID is not mapped for both login and user) if we have not migrated the logins with sp_help_revlogin script or by any other means, so users might have issues accessing the databases or application malfunctions. We need to fix the orphans users using system stored procedure sp_change_users_logins with appropriate parameters. SQL Server 2012 Onwards we have a new solution termed as contained database. Contained database is defined as a database which has the database user without logins. It includes all settings related to databases along with its metadata, thus system will be having no dependency with SQL server login. Users can easily connect to a contained database without having to go through the log in at DB engine. Contained database feature provides two containment modes: None – By default each database has its mode set as NONE. This means there is no contained database feature being used. Partial – With partially contained databases, we can define boundaries between databases and the server, so the metadata will exist inside the databases. It makes SQL Server databases more portable and less dependent on underlying hosts. Contained databases feature is available at instance level and it is not enabled by default. To enable it, right click on server ➜ properties, go to Advanced, and enable the Enabled Contained Databases option Alternatively, we can use a sp_configure system stored procedure to enable contained databases on the instance, as per below query: 12345678910 EXEC sp_configure 'show advanced', 1GORECONFIGUREGOEXEC sp_configure 'contained database authentication', 1GORECONFIGUREGOCreating a new contained database
If we want to create a new database as a contained database, we have to make containment type as Partial in the Options page. If we script out the create database, we can find out the query to create it using t-SQL as below: 1234567891011 CREATE DATABASE [TestContainedDB] CONTAINMENT = PARTIAL ON PRIMARY ( NAME = N'TestContainedDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestContainedDB.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'TestContainedDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestContainedDB_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%)GO Once the database is created, we can verify it using the sys.databases 123 select containment,name from sys.databases where name='TestContainedDB' If the containment is not enabled in the databases, it will return 0, else 1, so in our case it should return 1 for TestContainedDB as shown: Once the contained database is created, we need to create a new database user. For this, we need to expand contained databases and go to security -> Create new database user and create new user type as SQL user with password, provide default schema, appropriate permissions required, as shown below: The script below can be used to create the user by t-sql 123456 USE [TestContainedDB]GOCREATE USER [TestUser] WITH PASSWORD=N'test', DEFAULT_SCHEMA=[dbo]Go So here we need not to create the Login before creating the user, we will directly create the user with appropriate password tagged. Note: we cannot create the user in a normal database, which is not contained. If tried, we will get the below error Msg 33233, Level 16, State 1, Line 1 You can only create a user with a password in a contained database.How to connect to the contained DB using SQL Server Management Studio
Normally, to connect with SQL database instance we used to provide instance name, authentication method (windows\SQL) and, if SQL, username and password. But to connect contained database we also need to specify contained DB name in the connection parameter. If we try to connect contained database with contained user and password without specifying database name, we will get the error as: So, in the connection properties we need to specify contained database name as shown below: Once connected through SQL Server Management Studio, we can see the database user has access to:Converting existing database to the contained database
If want to convert existing database into contained database, run the command below: 1234 ALTER DATABASE [Database_name] SET CONTAINMENT = PARTIAL WITH NO_WAITGO After converting the database we need to take care of the existing logins as well. For this, we need to move the users or migrate the users using the system procedure. We need to use sp_migrate_users_to_contained with below parameters. @username = SQL Server authenticated user. @rename = If login and username differ, this tells whether to keep the user name or take the login name. @disablelogin = This parameter will disable to server login in the master database, if we want to Suppose we have the user as TestMovecontained, so query for moving the user will be: 1234567 sp_migrate_user_to_contained @username = 'TestMovecontained', --Userame to be specified here @rename = N'keep_name', @disablelogin = N'do_not_disable_login' ;GOWorking on a contained database
As a contained user is created at database level, we cannot do any activity we require instance permissions like backup \ restore. As visible below, we are not getting option of backup \ Restore database here but we can take the database Offline.Listing out logins that are of contained user type
We can use the system view sys.database_principals view in the database to see which users are listed as contained users using the below query 1234 SELECT name,type_desc,authentication_type_descFROM sys.database_principals WHERE authentication_type =2Benefits of using contained databases
It is quite easy to move the database from one server to another, as we will not have orphaned user issues Metadata is stored on contained databases so it is easier and more portable. We can have both SQL Server and Windows authentication for contained DB users.Disadvantages
Partially contained databases cannot use features like replication, change data capture, change tracking, schema-bound objects that depend on built-in functions with collation changes. Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022