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;
RECONFIGURE;

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

Alter DATABASE demodb
Set CONTAINMENT = PARTIAL;

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

USE demodb

CREATE USER TotallyContained

WITH PASSWORD = 'A)$*LaJSFS';

 

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.

 

 

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s