46

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
Ben
  • 51,770
  • 36
  • 127
  • 149
Faisal Ashfaq
  • 2,545
  • 4
  • 28
  • 41

3 Answers3

71

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.

Alpi Murányi
  • 1,117
  • 10
  • 17
  • 1
    This was the most helpful answer, it worked without messing with any configuration. – Alejandro Morán Feb 22 '21 at 17:49
  • 5
    The question is about contained database users, so whilst this answer might work for some it's not correct for a contained database, which does not use users based on logins. – Chris Mar 15 '21 at 09:50
44

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
Bakudan
  • 19,134
  • 9
  • 53
  • 73
buzzard42
  • 456
  • 5
  • 4
10

I think your current database is [master], but you have to use [MyDb]

USE [MyDb]
GO
CREATE USER MyUser
WITH PASSWORD = 'pass@123';
GO
Aleksandr Fedorenko
  • 16,594
  • 6
  • 37
  • 44
  • You may need same database configuration:https://dba.stackexchange.com/questions/20424/creating-user-for-a-single-contained-database/20428#20428?newreg=f9bfaa5987564bbb8fd931f782eea4ef – pdem Apr 18 '17 at 14:14
  • 7
    Doesn't work on MSSQL server 2017. still says the same error. – EngineSense Aug 02 '18 at 13:44