To create contained database users:
- Log into the SQL 2012 server using SQL Server 2012 Management Studio. You will need to use your primary SQL 2012 DB user.
- Open query analyzer and run following 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, make sure 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 generate an error.
Article ID: 20, Created: June 27, 2012 at 12:01 AM, Modified: February 5, 2014 at 8:40 PM