CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Faisal Ashfaq
Faisal Ashfaq

Reputation: 2678

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

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

Upvotes: 53

Views: 51725

Answers (3)

buzzard42
buzzard42

Reputation: 496

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

Upvotes: 48

Alpi Murányi
Alpi Murányi

Reputation: 1233

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.

Upvotes: 73

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

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

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

Upvotes: 10

Related Questions