Tip

Configuring and implementing a SQL Server contained database

Before you can configure a SQL Server contained database, you must enable containment on your instance of SQL Server 2012. To do so, run the sp_configure system stored procedure and set the contained databaseauthentication option to 1, as shown in the following T-SQL script:

EXEC sp_configure 'contained database authentication', 1;
GO

RECONFIGURE;
GO

As you can see, you must also run the RECONFIGURE statement for your setting to be implemented. Once you've done so, you're ready to set up a SQL Server 2012 contained database. In your database definition, include the CONTAINMENT clause and set the containment type to PARTIAL, as shown in the following example:

USE master;
GO

CREATE DATABASE ContainedDB
CONTAINMENT = PARTIAL;
GO

You can just as easily include the

    Requires Free Membership to View

CONTAINMENT clause in an ALTERDATABASE statement. In either case, once you've set up the database to be contained, you're ready to add a contained user. In the following T-SQL script, the CREATE USER statement defines the cduser1 account and assigns a password and default schema:

USE ContainedDB;
GO

CREATE USER cduser1
WITH PASSWORD = N'Pa$$word',
DEFAULT_SCHEMA = dbo;
GO

EXEC sp_addrolemember 'db_owner', 'cduser1'
GO

Notice that the CREATE USER statement is followed by the sp_addrolemember stored procedure, which assigns the user to the db_owner role.

Figure 1. You can create a connection specifically to a contained database.

As the examples have demonstrated, you need to take only three steps to set up a SQL Server 2012 contained database: Enable containment, configure the database for containment, and create the contained user. And if you don't want to script these settings, you can instead use the SQL Server Management Studio (SSMS) interface to configure them.

After you set up your contained environment, you should try to connect to the contained database by using the contained user to test the connection. In Object Explorer in SSMS, click the Connect button and then click Database Engine. When the Connect to Server dialog box appears, enter the instance name and credentials for the contained user, as shown in Figure 1.

Figure 2. You must specify the contained database in your connection.

If you click the Connect button at this point, you'll receive an error. That's because you must first specify the target database when connecting with a contained user account. (The database must be part of the connection string.) To add the database, click the Options button and type the name of the target database into the Connect to database text box, as shown in Figure 2.

Now when you click Connect, you'll be connected to the specified instance, with access only to the contained database. Figure 3 shows the connection in Object Explorer. Notice that the user and database names are included with the instance name. Also notice that only the ContainedDB database is included in the list of databases and that cduser1 is listed as one of the user accounts.

Figure 3. A connection to the contained database lists the user and database.

When working with contained databases, you'll often want to ensure they're as fully contained as possible. SQL Server 2012 provides two handy tools for working with containment:

  • Sys.dm_db_uncontained_entities: A system view that lists any noncontained objects in the database. You can use this view to determine what items to address to ensure your database is as contained as possible.
  • Sp_migrate_user_to_contained: A system stored procedure that converts a login-based user to a contained user. The stored procedure removes any dependencies between the database user and the login accounts.

By using these tools, you can achieve a status of full containment, making it easier to manage the database going forward.

About the author
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at http://www.rhsheldon.com.

This was first published in July 2013

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.