How do I create a new user in SQL Server 2012 that I can use in a connection string?

Question

I wasn't sure if this was a SO or SU question.

I have freshly installed SQL Server 2012.

I have created a database.

I have a visual studio 2012 project, and I want to connect to the database using a connection string in my web config.

Since I only have two accounts (sa and my windows account) I want to create a new user that will only have access to this one database.

Google says, in SSMS, expand the database, right click Security and go to New User.

However, when I do this and try to create a user, I choose SQL user with login, enter a username and login name, and get the error:

'news_login' is not a valid login or you do not have permission

I've tried by logging into SSMS as 'sa', logging in as my windows account, and running SSMS with administrator permissions. I've tried a few different usernames, and to no avail.

I notice there is no password box, so I fear I am doing it all wrong. Any suggestions?

Answer 1

Before you add the user to the db, add the user to the server -- under the server, right click the security folder, and try it that way. Then you can grant permissions to the appropriate databases to that user

Answer 2

Here's SQL way of doing this:

CREATE LOGIN NewUser   
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';  
GO  

  CREATE USER NewUser FOR LOGIN NewUser;  
GO  

EDIT: As per comment by @bschipp

Another example that does not expire password would be following

CREATE LOGIN NewUser   
    WITH PASSWORD = 'Uuxwp7Mcxo7Khy$#' CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;
GO
    CREATE USER NewUser FOR LOGIN NewUser;  
GO  

more on options you can read here.

Answer 3

First create a global login under Security -> Logins

Then add the user for the selected database in Databases -> Database -> Security -> Users