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;
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:
CREATE DATABASE ContainedDB
CONTAINMENT = PARTIAL;
You can just as easily include the 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:
CREATE USER cduser1
WITH PASSWORD = N'Pa$$word',
DEFAULT_SCHEMA = dbo;
EXEC sp_addrolemember 'db_owner', 'cduser1'
Notice that the CREATE USER statement is followed by the sp_addrolemember stored procedure, which assigns the user to the db_owner role.
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.
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.
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.
Use scripts to create SQL Server databases