You can only create a user with a password in a contained database

Question

I am using a contained database, after creating the database I attempted to create a user but I got the error:

You can only create a user with a password in a contained database

My code is:

sp_configure 'show advanced options',1 GO RECONFIGURE WITH OVERRIDE GO sp_configure 'contained database authentication', 1 GO RECONFIGURE WITH OVERRIDE GO 

CREATE DATABASE [MyDb] CONTAINMENT = PARTIAL ON PRIMARY ( NAME = N'My', FILENAME = N'C:\My.mdf') LOG ON ( NAME = N'My_log', FILENAME =N'C:\My_log.ldf') 

CREATE USER MyUser WITH PASSWORD = 'pass@123'; GO 

Answer 1

Create the login and the user separately:

CREATE LOGIN MyUser WITH PASSWORD = 'pass@123'; CREATE USER MyUser FOR LOGIN MyUser;   

The names can be the same but of course they can also be different.

Answer 2

In SQL Server 2017, I found that my initial setup did not configure the "contained databases" feature to help this along.

so, at server level you can check the server properties in the UI or run:

EXEC sp_configure 'CONTAINED DATABASE AUTHENTICATION' 

if the running value isn't 1, then:

EXEC sp_configure 'CONTAINED DATABASE AUTHENTICATION', 1 GO RECONFIGURE GO 

At the database level, it also might not have the "contained database" feature fully enabled. The option sits in the Database Properties panel on the Options section, the fourth dropdown at the top...

Containment type == None or Partial

You can set it via SQL too. eg:

USE [master] GO ALTER DATABASE [MyDb] SET CONTAINMENT = PARTIAL GO 

thereafter, you can create the contained user as suggested by @aleksandr

USE [MyDb] GO CREATE USER MyUser WITH PASSWORD = 'pass@123'; GO