SQL70527 error in database project

Question

I have a database-project, created based on my existing database. It also added the scripts for creating users. One of those scripts is =>

CREATE USER [JOOS_NT\Indigo.Development] FOR LOGIN [JOOS_NT\Indigo.Dev.svc];

This script works fine on my database. But in my database-project this script is throwing an error when I build it. The error is:

"SQL70527: 'JOOS_NT\Indigo.Development' is not a valid name because it contains characters that are not valid."

It seems the "\" in the [JOOS_NT\Indigo.Development] is not allowed. However on the database itself I can run the query and it works fine. If I change it to [JOOS_NT/Indigo.Development] I don't get the error, but when comparing the scripts in the project to the existing database, it would drop the user ([JOOS_NT\Indigo.Development]) and replace it with ([JOOS_NT/Indigo.Development])

What am I missing?

Answer 1

Answering for someone who will look in the future.

On the database project, if a '\' character is included in the user name, the login should match the user name.

Because that, this don't work:

CREATE USER [JOOS_NT\Indigo.Development] FOR LOGIN [JOOS_NT\Indigo.Dev.svc];

But this will:

CREATE USER [JOOS_NT\Indigo.Development] FOR LOGIN [JOOS_NT\Indigo.Development];

Or

CREATE USER [JOOS_NT\Indigo.Dev.svc] FOR LOGIN [JOOS_NT\Indigo.Dev.svc];

I'm not sure if this is the expected behavior or a bug.

Answer 2

This is by design. The core issue is that in the "CREATE USER FOR LOGIN" based on a windows user login, if you are using a domain name for the user then this must match the login's domain name + login name. See this post on MSDN