20

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?

1
  • 2
    This question has the same error message as this other question, but happens during normal administration rather than during installation. It looks like there are different causes and answers, so I'm posting this as a separate question.
    – culix
    Sep 18, 2014 at 13:06

3 Answers 3

24

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.
3
  • what is the meaning of "a global Login" ?
    – WhyWhat
    Oct 8, 2019 at 10:18
  • 1
    I guess we'll never know...
    – Denny
    Dec 17, 2020 at 13:04
  • A Login can only be created in the <servername>/Security directory. That is what he means with 'global Login', as it's one that can be used by all databases in the server. After creating said login you can create the user in the <servername>/<database>/Security directory.
    – baltermia
    May 4, 2021 at 8:36
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';
1

This worked for me :

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.