Trying to add a new SQL Server user throws the error "is not a valid login or you do not have permission”

Question

I am trying to add a new user to an SQL Server 2012 database using SQL Server Management Studio. I right-click and select 'Add User' under Security -> Users, fill out the user information, and click OK. Management Studio throws the the error message "is not a valid login or you do not have permission”.

How can I add this user?

Answer 1

Before adding a user one must first create a login. Logins can only be created in the servers Security-folder (not in the databses Security).

So the login can be created under <server>/Security -> Right-Click -> New -> Login....

A user with the same name and settings will automatically be added to the databases selected in the User Mapping tab when creating the login.

• If the user wasn't already created you can create it via <server>/<database>/Security -> Right-Click -> New -> User. Note that the names of the users and logins must match.
• If you would like to manage the users settings when it was automatically added you can change them in <server>/<database>/Security/Users/<user> -> Right-Click -> Properties.

Answer 2

If you are trying to create a user under the Contained database user model (so no global login for it), you have to provide a password during creation like this:

CREATE USER user_name WITH PASSWORD = 'strong_password';

Answer 3

This worked for me :

CREATE USER [chris23] FROM LOGIN [##MS_PolicyEventProcessingLogin##];
EXEC sp_addrolemember N'db_datareader', 'chris23';
GO

Answer 4

This is a bug in Microsoft SQL server, you need to create login in the master database first and then a user in your database:

When you're connected to the master database:

CREATE LOGIN login_name WITH PASSWORD = 'strong_password';

Then, when you're connected to a user database:

CREATE USER 'user_name' FOR LOGIN 'login_name';