How to connect to the contained database without creating the login and user on secondary instance

This blog is about contained database a SQL server feature introduce from SQL server 2012. This contained database are different from regular uncontained databases. Contained database don’t depend on SQL server instance. it’s configuration, database setting, meta data all contained within the database which includes user information.

The issue with regular uncontained databases is, when you move your database from instance to another, you have to move all the user of that database. Sometimes there will be orphan users for that you have to write and run the script. So in contained database you will not get this type of issue.

Before you go to create contained database you need to enable contained database feature, by default this feature is not enabled.

To Enable Contained database feature:

1. Right click on instance → click properties

2. In left pane click Advance

Change Enable Contained Databases as true and click ok.

Or you can use sp_configure stored procedure to enable contained databases on the instance, as per below query:

EXEC sp_configure ‘show advanced’, 1

GO

RECONFIGURE

GO

EXEC sp_configure ‘contained database authentication’, 1

GO

RECONFIGURE

GO

After enabling the contained databases feature you can head to create contained database.

To create contained database:

1. In object explorer Right click on database → New database and provide a name for the database.

2. Click option in the left pane and select partial in containment type.

3. Click OK.

Now we have to create a user for this database.

To create a user

1. Expand Database → Expand the contained database which you created

2. Then expand security

3. Right click Users → New user

4. Provide user name and password

5. In Owned schema select db_owne

6. Click ok.

Now to check the user access

1. Click connect and enter username and password.

If you click connect you will get the following error. ‘Login failed for user’

Click Options and go to connections properties and type contained database name in connect to database and then click connect.

Now you will be connected only to that database without server login.

You can also check by taking the backup of this contained database and restore to another instance and no need to create this user on another instance.

To take backup of Contained Database:

1. Right click that database → tasks → Backup

2. Choose the backup path

3. Click ok.

To restore the Contained Database in another instance:

1. Right click Databases → Restore Database

2. Select the backup file from that path in Device

3. In Files select Relocate all files to folder

4. Click ok.

Now you don’t have login or that user in this instance but you can connect to this database using that user

1. Click connect from object explorer

2. Enter the username and password

3. Click option

4. Enter the contained database name in Connect to database:

5. Click Connect.

That’s it now you are able to connect to the contained database without creating the login and user on secondary instance.