0

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.

0

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.

  • 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. – MartW Sep 20 '09 at 4:20
  • Next question : have you set the Default database of your new login to one it has access to? – MartW Sep 20 '09 at 4:21
  • Yes I did. I did that under Logins>Default Database – epitka Sep 20 '09 at 12:02
0

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

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.