Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a installed 2005 sqlexpress as well as express manager studio. I added a login under Logins, associated user with my database and gave it db_owner role under User mapping.

Now I right click on the server, under properties change from windows authentication to sql server authentication, enter login and password, click test and I get error:

Login failed for user... User is not associated.. Error: 18452.

share|improve this question

2 Answers

up vote 0 down vote

Did you also change the Server to use Mixed Mode rather than just Windows Authentication? If so, you need to restart the server for this to take effect.

share|improve this answer
That's the thing, I read about this mixed mode, but I don't have it when I right click on the server>properties. I just have Sql and windows authetntication, no mixed mode. – epitka Sep 20 '09 at 3:44
Sorry, confusing term. Mixed Mode just means both authentication types are available, and you've done this. – CodeByMoonlight Sep 20 '09 at 4:20
Next question : have you set the Default database of your new login to one it has access to? – CodeByMoonlight Sep 20 '09 at 4:21
Yes I did. I did that under Logins>Default Database – epitka Sep 20 '09 at 12:02
up vote 0 down vote accepted

I looked more into that Mixed Mode suggested by CodeByMoonlight, and found that I had to change the registry entry since MIXED MODE WAS NOT ENABLED, even though I had windows and sql authetntication as login methods in properties tab. To change to mixed mode run regedit from command line and change this entry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

LoginMode =2

If you need to have sysadmin access, and if you don't know 'sa' password you can add new one like this:

SQLCMD -S.\SQLEXPRESS -E

sp_addLogin ‘username’, ‘pass’ GO sp_addsrvrolemember ‘username’, ’sysadmin’ GO

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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