Rajendra Gupta

Contained databases in SQL Server

January 12, 2016 by

As we know there are two types of authentication available in SQL Server Windows authentication and SQL authentication. In Windows authentication we use Active directory authentication to connect with SQL Server which makes the most secure authentication method as it can have complexity, group policy configured at AD level applied to all domain servers while in SQL Authentication SQL users are created inside SQL and provided required permissions. The Permissions includes server wide and database wide. The logins can have certain permissions at the database level might be read or write etc.

We need to understand the difference between a user and login here. A login is created at a server level while the user is created at a database level. Each having Unique SID and to work for the user, both SID should be equivalent.

Usually, we have the requirement for restoring the staging or UAT databases from production or any new server. When we move or restore the database into a different environment we might come up with issues of Orphan users (where SID is not mapped for both login and user) if we have not migrated the logins with sp_help_revlogin script or by any other means, so users might have issues accessing the databases or application malfunctions. We need to fix the orphans users using system stored procedure sp_change_users_logins with appropriate parameters.

SQL Server 2012 Onwards we have a new solution termed as contained database. Contained database is defined as a database which has the database user without logins. It includes all settings related to databases along with its metadata, thus system will be having no dependency with SQL server login. Users can easily connect to a contained database without having to go through the log in at DB engine.

Contained database feature provides two containment modes:

  • None – By default each database has its mode set as NONE. This means there is no contained database feature being used.
  • Partial – With partially contained databases, we can define boundaries between databases and the server, so the metadata will exist inside the databases. It makes SQL Server databases more portable and less dependent on underlying hosts.

Contained databases feature is available at instance level and it is not enabled by default.

To enable it, right click on server ➜ properties, go to Advanced, and enable the Enabled Contained Databases option

Alternatively, we can use a sp_configure system stored procedure to enable contained databases on the instance, as per below query:

Creating a new contained database

If we want to create a new database as a contained database, we have to make containment type as Partial in the Options page.

If we script out the create database, we can find out the query to create it using t-SQL as below:

Once the database is created, we can verify it using the sys.databases

If the containment is not enabled in the databases, it will return 0, else 1, so in our case it should return 1 for TestContainedDB as shown:

Once the contained database is created, we need to create a new database user.

For this, we need to expand contained databases and go to security -> Create new database user and create new user type as SQL user with password, provide default schema, appropriate permissions required, as shown below:

The script below can be used to create the user by t-sql

So here we need not to create the Login before creating the user, we will directly create the user with appropriate password tagged.

Note: we cannot create the user in a normal database, which is not contained. If tried, we will get the below error

Msg 33233, Level 16, State 1, Line 1 You can only create a user with a password in a contained database.

How to connect to the contained DB using SQL Server Management Studio

Normally, to connect with SQL database instance we used to provide instance name, authentication method (windows\SQL) and, if SQL, username and password.

But to connect contained database we also need to specify contained DB name in the connection parameter.

If we try to connect contained database with contained user and password without specifying database name, we will get the error as:

So, in the connection properties we need to specify contained database name as shown below:

Once connected through SQL Server Management Studio, we can see the database user has access to:

Converting existing database to the contained database

If want to convert existing database into contained database, run the command below:

After converting the database we need to take care of the existing logins as well. For this, we need to move the users or migrate the users using the system procedure. We need to use sp_migrate_users_to_contained with below parameters.

  • @username = SQL Server authenticated user.
  • @rename = If login and username differ, this tells whether to keep the user name or take the login name.
  • @disablelogin = This parameter will disable to server login in the master database, if we want to

Suppose we have the user as TestMovecontained, so query for moving the user will be:

Working on a contained database

As a contained user is created at database level, we cannot do any activity we require instance permissions like backup \ restore.

As visible below, we are not getting option of backup \ Restore database here but we can take the database Offline.

Listing out logins that are of contained user type

We can use the system view sys.database_principals view in the database to see which users are listed as contained users using the below query

Benefits of using contained databases

  1. It is quite easy to move the database from one server to another, as we will not have orphaned user issues
  2. Metadata is stored on contained databases so it is easier and more portable.
  3. We can have both SQL Server and Windows authentication for contained DB users.

Disadvantages

Partially contained databases cannot use features like replication, change data capture, change tracking, schema-bound objects that depend on built-in functions with collation changes.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. SQL Server 2014 contained databases
  2. How to migrate users to a partially contained database in SQL Server
  3. Recover a lost SA password
  4. Microsoft SQL Server Non-Contained Object Migration Deployment Procedure using Powershell
  5. How to migrate the logins of a database to a different server
168 Views