Have you ever wondered how to make your SQL Server databases more portable and self-contained? In this blog post, we will discuss the concept of Contained Databases in SQL Server and how they can enhance the security and portability of your databases.

A Contained Database is a database that contains all the necessary settings and metadata, making it easily portable to another server. Unlike traditional databases, a contained database does not depend on the server where it is installed for anything. This means that you can take a contained database and move it to another server without any worries.

But what about the users who are connecting to this database? With contained databases, the users are also moved along with the database. Users who belong to a contained database will have no access outside of that database. This makes the database self-contained and independent of the server where it is installed.

Let’s try out this feature on SQL Server. We will go through the following steps:

1. Enable Contained Database
2. Create a Contained Database
3. Create a User in the Contained Database
4. Test if the User can access outside the Contained Database

Enable Contained Database

To enable the settings for a contained database, you can run the following code on SQL Server:

sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO

Create a Contained Database

To create a contained database, you can use the following code:

CREATE DATABASE [ContainedDatabase]
CONTAINMENT = PARTIAL
ON PRIMARY (
    NAME = N'ContainedDatabase',
    FILENAME = N'C:\ContainedDatabase.mdf'
)
LOG ON (
    NAME = N'ContainedDatabase_log',
    FILENAME = N'C:\ContainedDatabase_log.ldf'
)
GO

Create a User in the Contained Database

To create a user in the contained database, you can use the following code:

USE [ContainedDatabase]
GO
CREATE USER ContainedUser WITH PASSWORD = 'pass@word';
GO

Test if the User can access outside the Contained Database

To test if the user can access outside the contained database, you can try to log in to the server using the username created in the contained database. You will notice that the login will be successful, but the user will only have access to the contained database and not to any other database.

Contained databases provide a great way to enhance the security and portability of your SQL Server databases. By making the database self-contained, you can easily move it to another server without worrying about dependencies or user access outside of the database.

In future blog posts, we will explore more about this interesting subject and dive deeper into the capabilities and benefits of contained databases in SQL Server.