How to create contained DB users?

By
Claire Y.
-
0
2523

To create contained DB users:

  • Log into the SQL 2012 server using SQL Server 2012 Management Studio. You will need to use your primary SQL 2012 DB user.
  • Click on 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.
  • Input the password to this contained DB User.
  • On the “Select a page” column click on Membership.
  • Click on db_owner.


Note: When logging into the SQL 2012 server using SQL Server 2012 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 2012 Management Studio will automatically try to authenticate to the Master database which will give you an error.