How to create contained database users

To create contained database users:
  • These instructions apply to SQL 2012 and later.
  • Log in to the SQL server using SQL Server Management Studio using your primary SQL DB user.
  •  Create a 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 on the Security folder and choose "New User..."
  • Choose "SQL user with a password" for the User Type.
  • Enter the password to this contained DB User.
  • On the "Select a page" column, click "Membership."
  • Click db_owner.
Note: When logging in to the SQL server using SQL Server Management Studio, make sure that default database connection is set to point to your database. When connecting to database, put in newly created users credentials, but click on Options instead of Connect. 









Then in the field for "Connect to database" input the specific database the user has access to.