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.
  •  Open query analyzer 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. 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.
In addition to backing up your site files, our SiteBackup service can also back up your databases. Now you can control backup retention and schedule. Automate your backups with SiteBackup.