SQL Server provides two ways to authenticate users; SQL Server Authentication, which requires a predefined username and password to connect to the SQL Server, and Windows Authentication, in which SQL Server trusts the windows integrated user. The server level user that is authenticated to connect to the SQL Server is called a Server Login. This login should be mapped to a database user and granted permissions at the database level in order to access the database and be able to perform the authorized tasks. The relationship between the Server login and the database user in addition to the database metadata stored in the master system database draw up the dependencies between the SQL Server databases the server-level resources.
The dependency of a SQL Server database on the server-level resources could cause problems especially when you manage to copy, move or restore a database to another SQL instance, where the entities that are external to the database, such as the logins, are not there. So you must define these logins on the new instance, otherwise, the mapping will be broken and these users will not be able to connect to the database, and these database users will be considered orphaned users.
In SQL Server 2012, Microsoft introduced a new concept that defined the database borders, which includes the database settings and metadata, this concept is called Boundaries. Specifying the boundaries make it easy to isolate the database from the external server-level dependencies, including the logins. This isolation feature is called Containment.
A contained database is a database that is isolated from the SQL Server instance hosting that database and other databases located in the same instance. Database containment can be Full, Partial or None. Only Partial containment and None containment types are supported in SQL Server. A fully contained database has no dependencies on the SQL Server instance that hosts it, as the database contains its metadata and settings. The partially contained database contains only the possible objects that can be managed by the database. None containment is the default setting for the databases, in which all entities are managed by the SQL instance.
As a benefit from the contained database, user authentication can be performed at the database level, without the need to have a mapped server login. This will overcome the errors related to the orphaned users. Contained database users can be Windows authenticated or SQL Server authenticated users.
Contained entities are user entities that are located inside the database boundaries. Entities located outside the database or interact with external entities are considered uncontained entities. Contained database users are able to access only database objects. The partial containment allows the user to use uncontained entities too. The user that is created inside the partially contained database will have access to the master and TempDB system databases as a guest, and will be accessing it if the guest user is enabled on it.
To support SQL Server contained databases, ALTER DATABASE and CREATE DATABASE statements are modified in SQL Server 2012 to include the CONTAINMENT argument to change the database containment status between None and Partial containments. Also, the ALTER DATABASE statement includes the CURRENT argument in order to make changes on the current database directly without specifying the database name.
Enabling the SQL Server containment feature at the server level, you can create a database or ALTER the current database to be able to store its settings and metadata by itself, and create database users without the need to create server logins and map it to that database users.
Another benefit from the contained database, when your database collation is different from the hosting server’s collation, temporary database objects will be created using the contained database collation rather than the TempDB collation in a non-contained databases situation, in which you have to use the COLLATE statement to resolve collation compatibility issues.
The SQL Server contained database feature is useful in resolving many problems you may face with non-contained databases. The first problem is when you copy or move databases from one SQL Server instance to another one, where the login information which is stored in the source SQL instance will not be available in the new instance. To overcome this issue with non-contained databases, you need to create these missing logins in the new instance, which requires time and effort. This issue is completely resolved in the case of contained databases, where the partially contained database can store its information after moving the database to the new server, without consuming time in defining the server logins. The contained database feature is not enabled in the SQL Server instance level by default. So you need to make sure that you enable it in the instance where you moved or copied the contained database.
Reducing the level of database dependency on the SQL Server instance by using a contained database is useful when you configure the SQL Server Always On Availability Groups. In the case of uncontained databases, server logins on the primary server should be created in the secondary replicas to make sure that the users can connect during the failover. If you use contained databases, the users will be able to connect during the failover without the need to create logins on all replicas and check the mapping.
Contained database are useful also for database developers who have no idea where the database will be deployed in the production environment, which differs from the development environment. So, the developer can concentrate on his development tasks without considering the impact of the database deployment.
Enabling database containment will allow each database owner to manage his/her own database, without having sysadmin permissions at the server level, as the contained database settings are maintained in the database itself, not the master system database.
The sys.dm_db_uncontained_entities system view is a useful tool to identify the status of the contained database. From the name of the view, it shows all uncontained database entities due to dependencies of these entities on the server-level entities, which you need to consider when moving the contained database to another SQL Server instance.
The below simple query is used to show uncontained entities within the SQLShackDemo contained database:
|
1 2 3 4 5 6 |
USE SQLShackDemo GO SELECT class, class_desc ,major_id ,feature_name ,feature_type_name FROM sys.dm_db_uncontained_entities |
You can easily join the previous query with the corresponding system table to get the object name. For example, the previous query can be joined with the SQLShackDemo.sys.database_principals system table to get the principals that has dependencies with other databases in the same server as follows:
|
1 2 3 4 5 6 7 8 |
USE SQLShackDemo GO SELECT class, class_desc ,major_id ,feature_name ,feature_type_name , SP.name FROM sys.dm_db_uncontained_entities UE JOIN SQLShackDemo.sys.database_principals SP on UE.major_id =SP.principal_id |
SQL Server partially contained databases have few limitations. Any database configured with partial containment can’t use the SQL Server Replication, SQL Server Change Data Capture (CDC) or SQL Server Change Tracking (CT) features.
Let’s start with configuring the contained database. To use the contained databases, we should enable the containment feature at the SQL Server Instance level first. This can be easily achieved by using the sp_configure stored procedure as below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE master GO sp_configure 'show advanced options', 1 GO RECONFIGURE GO sp_configure 'CONTAINED DATABASE AUTHENTICATION', 1 GO RECONFIGURE GO sp_configure 'show advanced options', 0 GO RECONFIGURE GO |
You can also enable containment using SQL Server Management Studio from the Advanced page of the Server Properties window, by setting the Enable Contained Databases option to True as follows:
Once the containment feature is enabled at the SQL instance level, we can enable it at the database level. What is required here is to change the database containment option from None to Partial. You can perform this operation using the ALTER DATABASE T-SQL statement below:
|
1 2 3 4 5 6 |
USE [master] GO ALTER DATABASE SQLShackDemo SET CONTAINMENT = PARTIAL GO |
Again, you can perform the same process using SQL Server Management Studio from the Options page of the Database Properties window by setting the Containment type to Partial as follows:
Now the SQLShackDemo is configured as a partially contained database. To have a full demo, we will create a SQL authentication contained user, which is created at the database level without mapping it to a server login. The user can be created using the Create User T-SQL statement as below:
|
1 2 3 4 5 6 7 8 9 10 |
USE [SQLShackDemo] GO CREATE USER [SQLShackDemoUser] WITH PASSWORD=N'SQLSCKdem_123456' GO USE [SQLShackDemo] GO ALTER ROLE [db_owner] ADD MEMBER [SQLShackDemoUser] GO |
Using the SQL Server Management Studio, expand the database Security node and right-click on the Users node to choose New User option:
From the Database User – New page, simply fill the user name and the password fields and grant the user the required permissions from the Membership tab then click OK.
To check that this user is a contained user and has no mapped server login, we can query the sys.server_principals and the sys.database_principals system tables for that user as below:
|
1 2 3 4 5 |
SELECT name,type,type_desc from master.sys.server_principals WHERE name='SQLShackDemoUser' GO SELECT name,type,type_desc from SQLShackDemo.sys.database_principals WHERE name='SQLShackDemoUser' |
It is clear from the following result that the user is only created at the database level without mapping it to a login at the server level:
Another useful option is to change the already created SQL authentication or Windows authentication users to be contained database users using the sp_migrate_user_to_contained stored procedure, with the ability to change the user’s name and disable or keep the mapped login at the server level as follows:
|
1 2 3 4 5 6 7 8 9 |
USE [SQLShackDemo] GO sp_migrate_user_to_contained @username = N'suheir', @rename = N'keep_name', @disablelogin = N'do_not_disable_login' ; GO |
If you try to connect to the SQL instance using the contained user created previously you will get the Login failed error below:
What is missing here is that you need to specify the contained database name that the user has access on in the Connect to Database option from the Connection Properties window, after expanding the Options button in the Connect to Server window below:
Another way to achieve this, by specifying the contained database name as Initial Catalog in the Additional Connection Parameters page in the Connect to Server window as follows:
Click on the Connect button after specifying the database name, the user will connect to the SQL instance successfully and will be able to browse the SQLShackDemo database only as below:
If that contained user try to access any entities outside the SQLShackDemo database boundaries, he will not be able to access, getting the permission error below:
|
1 2 3 |
SELECT * FROM AdventureWorks2012.dbo.ErrorLog |
Conclusion:
SQL Server 2012 introduced the contained database concept, where the database has no dependencies on the SQL Server instance that hosts that database. This dependency between the database and the instance hosting that database is useful when you manage to move the database between SQL instances or use the database in a SQL Server Availability Group site without requiring time managing factors located outside the database boundaries.
See more
Consider these free tools for SQL Server that improve database developer productivity.
Useful links:
- Contained Databases
- Migrate to a Partially Contained Database
- sp_migrate_user_to_contained
- sys.dm_db_uncontained_entities
- Security Best Practices with Contained Databases
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021