Introduction
Microsoft introduced the Contained Database feature in SQL Server 2012. In this article we will take a look at how to migrate SQL Server Logins to a partially contained database in SQL Server to remove dependencies from the Instance of SQL Server where Contained Database resides.
Overview of contained database in SQL Server
A contained database is a user database which is completely isolated from the all the other databases which are residing on the instance of SQL Server.
Different types of containments supported by SQL Server
The different types of database containments are FULL, PARTIAL or NONE. However, Microsoft SQL Server 2012 and later versions currently support only PARTIAL and NONE containments.
Full Database Containment: A fully contained database will not have any configuration dependencies on the SQL Server Instance where the database is hosted. Hence, we can very well say that a fully contained database will contain all the required metadata and settings to define the database. This feature is still not available in any version or edition of SQL Server.
Partial Database Containment: A partially contained database is a contained database will contain all the necessary objects and will allow the use of uncontained features.
None Database Containment: Any database which has containment set to NONE. All the databases prior to SQL Server 2012 are non-contained. Starting SQL Server 2012 and later versions all database will have containment value set as NONE by default.
Key limitations of contained databases in SQL Server
Some of the key limitations of a contained database which you must consider before you plan to migrate to a contained database are:-
Replication, Change Data Capture, or Change Tracking should not be configured on the database which you would like to convert it into a contained database.
Partially contained databases should not have any procedures which have procedure names that include numbers.
Partially contained databases shouldn’t have any Schema-bound objects which depend upon any built-in functions with any collation changes
It is highly recommended to evaluate if there are any containment breaches for a user database before it is migrated to a partially contained database in SQL Server 2012 and later versions. There are different ways in which you can quickly identify containment status of any user database. Let us explore each of these options one by one.
Identity database containment breaches using sys.dm_db_uncontained_entities Dynamic Management View
Using sys.dm_db_uncontained_entities dynamic management view you could easily identify if there are any database containment breaches. If the database has any uncontained entries then this DMV will output the results including the once across database boundaries. However, if your database uses Dynamic SQL then those issues will not be outlined until run time.
|
1 2 3 4 |
SELECT * FROM sys.dm_db_uncontained_entities GO |
Identity database containment breaches using database_uncontained_usage Extended Event in SQL Server
Using database_uncontained_usage extended event you could find out if there are any uncontained entries at run time. Whenever, there is a containment breach identified during run time only those entries will be captured by this extended event. Hence, if there are any uncontained entries which are never called will go unnoticed until they are triggered.
Let us get started with a demo:
Firstly, we will be to enable contained database feature at SQL Server Instance Level
Secondly, we will enable containment type for a database.
Finally, we will convert all the existing SQL Server Login which has access to Contained Database to a contained use of the database with no dependencies on SQL Server Instance.
How to enable contained database feature in SQL Server 2012 and later versions
Open SQL Server Management Studio and then connect to the SQL Server Instance where your user database resides.
In Object Explorer, right click the SQL Server Instance and select Server Properties. Click on Advanced tab on the left pane and then change the value as True for Enable Contained Databases as shown in the snippet below and click OK to save the changes.
In Object Explorer, expand Databases nodes and then right click the database and select Properties. Click on Options on left pane and choose Containment Type value as Partial from the drop down menu as shown in the snippet below and click OK to save the changes.
You can also enable Contained Database feature at instance level by executing the below mentioned TSQL Script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
USE master GO EXEC sys.sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sys.sp_configure N'contained database authentication', N'1' GO RECONFIGURE GO EXEC sys.sp_configure 'show advanced options', 0 GO RECONFIGURE GO |
How to convert a user database to a partially contained database in SQL Server 2012 and higher versions
You can also change containment type of user database by executing the below mentioned TSQL Script.
|
1 2 3 4 5 6 7 8 |
USE [master] GO ALTER DATABASE [AdventureWorks2012] SET CONTAINMENT = PARTIAL WITH NO_WAIT GO |
How to identify whether all SQL Server logins in the contained database are contained users or not?
Execute the below query to identify the list of all the SQL Server Login which are yet to be contained users.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE AdventureWorks2012 GO SELECT sdp.name FROM sys.database_principals AS sdp JOIN sys.server_principals AS ssp ON sdp.sid = ssp.sid WHERE sdp.authentication_type = 1 AND ssp.is_disabled = 0 AND sdp.name <> 'dbo' GO |
In the above snippet you could see that there are three users which have access to our newly converted contained database and they are not contained users with dependencies on SQL Server Instance. Next step will be to migrate them to be contained users for the contained database.
How to migrate SQL Server logins to contained database users
Using sp_migrate_user_to_contained procedure you can convert any database user which is mapped to a SQL Server Login to a contained database user along with its password. Execute the below query to convert dbuser1 who has db_owner permissions from a non-contained user to a contained database user.
|
1 2 3 4 5 6 7 8 9 10 |
Use AdventureWorks2012 GO sp_migrate_user_to_contained @username = N'dbuser1', @rename = N'keep_name', @disablelogin = N'do_not_disable_login'; GO |
In the below snippet you could see that dbuser1 is successfully migrated as a contained user for AdventureWorks2012 contained database.
However, dbuser2 which also has access to contained database is still a non-contained user with dependencies on SQL Server Instance. The user dbuser2 and dbuser3 needs to be converted as a contained user to remove dependencies from the instance of SQL Server.
For this demo we had three users namely dbuser1, dbuser2 and dbuser3 which had access to AdventureWorks2012 database. Once the database is converted as a contained database the final step, will be to migrate all non-contained users to Contained Database as a contained user. If you have multiple logins as in our case you can execute the below TSQL script using a SA account within the scope of contained database to successfully migrate all the SQL Server Logins relevant to the contained database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Use AdventureWorks2012 GO DECLARE @MigrateContainedUsers sysname ; DECLARE MigrateContainedUsers CURSOR FOR SELECT sdp.name FROM sys.database_principals AS sdp JOIN sys.server_principals AS ssp ON sdp.sid = ssp.sid WHERE sdp.authentication_type = 1 AND ssp.is_disabled = 0 AND sdp.name <> 'dbo' OPEN MigrateContainedUsers FETCH NEXT FROM MigrateContainedUsers INTO @MigrateContainedUsers WHILE @@FETCH_STATUS = 0 BEGIN EXECUTE sp_migrate_user_to_contained @MigrateContainedUsers = @MigrateContainedUsers, @rename = N'keep_name', @disablelogin = N'disable_login'; FETCH NEXT FROM MigrateContainedUsers INTO @MigrateContainedUsers END CLOSE MigrateContainedUsers DEALLOCATE MigrateContainedUsers |
In the snippet below you could see that now all the three users are converted as a contained user of contained database and has no dependencies on SQL Server Instance.
Conclusion
In this article we explored how to identify containment breaches and then how to quickly migrate all SQL Server Logins of a Contained Database as a contained user of the contained database.
Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft and Cognizant.
He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.
View all posts by Ashish Kumar Mehta