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?

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. — sgeddes, May 05 '13 at 16:28

score 41 · Accepted Answer · answered May 05 '13 at 17:48

41

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

answered May 05 '13 at 17:48

sgeddes

62,311
6
61
83

2

This worked for me! Just wanted to add that even though the user was added, my SQL login wouldn't work. I had to change a setting in Server Properties -> Security -> Enable SQL Server and Windows Authentication mode. Hope others find this useful. – zeta Jul 07 '16 at 18:17
Changing the Server Properties did the trick for me. After creating the user, i was still getting the error - after updating server properties, and restarting the server, it is working. Thank you so much – Siraj Samsudeen Jul 05 '18 at 04:28

Matas Vaitkevicius · Answer 2 · 2018-04-12T01:09:30.927

11

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

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

3 Answers3

Linked