How to Create Contained DB Users

These instructions are for MS SQL 2012 and later databases.

To create contained DB users:

• Log in to the SQL server using SQL Server Management Studio.  Use your primary SQL DB user.
• Click "New Query" and run this SQL command against your database.

USE MASTER
GO
ALTER DATABASE [Database Name]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [Database Name]
SET CONTAINMENT  = PARTIAL
GO
ALTER DATABASE [Database Name]
SET MULTI_USER
GO

This will set the database containment type from "None" to "Partial".   By default your database is set to "None."

• Go to the Security folder under your database.
• Right click the Security folder and choose "New User..."
• Choose "SQL user with a password" for the User Type.
• Enter the password for this contained DB User.
• On the "Select a page" column click "Membership."
• Click on the db_owner.

When logging into the SQL server using SQL Server Management Studio, you will need to ensure that default database connection is set to point to your database.  If you leave it in its default state, SQL Server Management Studio will automatically try to authenticate to the Master database which will generate an error.