Cannot create sql server database user correctly

Question

I'm trying to create a user in a SQL Server database with SQL authentication. That's what I did:

Created database named testDb (with default parameters)

ran script

CREATE LOGIN test 
    WITH PASSWORD = 'test';
USE testDb;
CREATE USER test FOR LOGIN test;
GO

Ensured user test appeared in Databases\testDb\Security (in Management Studio)
Tried to connect to the server with Management Studio using user test
Got 18456 error.

I'm using SQL Server 2008 Developer Edition. The OS is Windows 7 Ultimate. What am I doing wrong?

I don't see anything that you're doing wrong - this should work just fine (and did on my machine just now)..... did you mistype the password by any chance?? ..... — marc_s
– marc_s, Commented Oct 9, 2011 at 8:08
no way. I tried many times (even in notepad). Might anything be wrong with server settings? — StuffHappens
– StuffHappens, Commented Oct 9, 2011 at 9:02
Have you configured the instance to allow mixed mode authentication and then restarted it? By default SQL Server logins are disabled. — Martin Smith
– Martin Smith, Commented Oct 9, 2011 at 9:21
No, I set up with default settings. It there a way to change it now? How? — StuffHappens
– StuffHappens, Commented Oct 9, 2011 at 9:57

Martin Smith · Accepted Answer · 2011-10-09 10:30:55Z

7

By default SQL Server logins are disabled.

You need to right click the instance in SSMS object Explorer then from the "Properties" dialogue, Security Tab enable "SQL Server and Windows Authentication mode".

This requires a restart of the SQL Server service to take effect,

answered Oct 9, 2011 at 10:30

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot create sql server database user correctly

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related