We mentioned the Contained Database in our article titled “How To Move Logins To Another Instance (sp_help_revlogin)“.
A Contained Database is a type of database that runs without login.
So, when we move the database to another instance, we do not have to transfer the logins.
We will be looking at how to create a Contained database and how to convert a database to a contained database.
Creating a Contained Database:
First of all, we need to make a configuration that allows us to create the contained database at the instance level with the following script.
I split the script into sections. You have to run them piece by piece in order.
|
1 2 3 4 5 6 7 8 9 10 |
USE master GO sp_configure 'show advanced options',1 reconfigure ----------------------------------------------------------------------------- sp_configure 'contained database authentication',1 reconfigure ----------------------------------------------------------------------------- sp_configure 'show advanced options',0 reconfigure |
Then we create our Contained Database.
You can design your database according to your needs, as I mentioned in the article “How to Create a Database on SQL Server“.
The only difference is that you need to set the Containment Type in the Options tab Partial as shown in the image below.
We need to create a user to connect to the Contained Database.
From the Security tab under the database you created on SSMS, right click on Users and click new user.
We enter the necessary information as shown in the screens below.
We gave db_owner database role in Owned_schemas.
You can define a more specific role if you like.
We created Contained Database named ContainedDB and User named ContainedDBUser.
Note that we did not create a server level login. We just created a user under the database and did not do any map operations.
We defined db role in owned schemas under database.
I will move the database to another instance using the backup restore method.
Note that we need to do the configuration we did at the beginning of the article on the instance we moved.
And let’s try to connect with SSMS without defining ContainedDBUser as login on that instance.
As you see below, we got a login failed for user error.
Well, when we moved the Contained Database to another instance, we said we would not transfer logins.
You might think why we get the login failed error.
Now, try to connect to Connect by clicking on Connection Properties on the connection screen and typing ContainedDB in the “Connect to database” field.
