Using Contained Database to allow users to be copied with SQL Database

Since SQL Server 2012 there is a feature of SQL server that helps make copying databases easier.  Its called contained database.  This will allow a database to be copied along with the user logins.

Normally user login information is stored in the Master Database on SQL Server.  That is why when you copy a database to another server the logins are missing and have to be scripted separately.   With the Contained Database this is no longer necessary.

First the feature has to be enabled on the server

sp_configure 'contained database authentication', 1;

Next the individual database has to be set to support containment.

Alter DATABASE demodb

Now we can add a user to the database with the following;

USE demodb

CREATE USER TotallyContained



The TotallyContained user only exists in the demodb database.  If I were to copy the database to another server, the TotalContained user will show up with sames access rights simplifing the process of moving or copying the database.




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s