Partially Contained Database’s in MS SQL Server

Contained Databases or (Partially Contained Databases, more on this later) to give them their correct title is a feature in SQL Server that essentially allows you to create a database where all of the components are contained within that database, even the logins, that’s right the logins can exist only in the contained database and do not need to exist on the server!!

The Upside of this? You guessed it, makes the migration of a database onto a new server, a much easier job as you no longer have to worry about migrating over logins or worry about orphaned users. Ok so enough chat on what it does, let’s get down into the weeds!

In a Partially Contained Database there are two types of authentication methods used:

• Window Contained Users
• SQL Contained Users

But regardless of the authentication method it happens at the database level and NOT the server level, this is key thing with contained databases.

1. First up you need to ensure contained database is enabled on the server:

exec sp_configure 'contained database authentication',1;
go
reconfigure;

2. Next, you need to set the database you are creating to partially contained or modify and existing database.

//Creating a new Database
create database mycontaineddb containment = partial;

//Modifying an existing database
Alter database myolddatabase set containment = partial with no_wait;

3. Lets create a SQL User that is fully contained in our database:

 Use mycontaineddb
 GO
 CREATE USER TestUser WITH PASSWORD = N'test*test*test1', default_schema = dbo;
GO

4. Let’s try connecting to the newly contained database:

• Normally to connect you would specify a Server Instance Name, Authentication method and Windows or SQL Login details. For Contained database you need to do the following:
  • In SSMS, click on Options
  • Choose the ‘Connection Properties’ tab
  • In the ‘Connect to database’ dropdown choose the contained database.

Well Partially Contained Databases just sound an amazing, ok then what are the drawbacks, there must be some right? Well, ok there are two, not necessarily drawbacks but more limitations.

1. Replication, CDC or Change Tracking cannot be enabled on a Partially Contained database.
2. Security – So it’s not they are less secure but rather the user that owns the database, i.e. in the role db_owner, would be able to grant access to the database to other users, which of course in turns means this user can now access the database server. This may or may not be an issue, but it is something to be aware of.

Just before I bring this month’s blog to a close, there is a very useful stored procedure that Microsoft provides us to help migrate over users to a contained database, it’s called sp_migrate_user_to_contained. What this proc does is to unlink the SQL Login from the user and assign the password of the Login to that user. You should then follow up on this by removing the SQL login after.

E.g.
            exec sp_migrate_user_to_contained
                @username = 'someolduser',
                @rename = N'keep_name'
                @disablelogin = N'disable_login'

Oh, I nearly forgot, why partially contained? Because there are still some features that cross the database boundary, so you would not say that it is fully contained.

Until next time, Slán!

Disclaimer: Never just believe everything you read in a post, test this out for yourself, we all can make mistakes! Happy coding.