10

So I was trying to change some settings in my Microsoft SQL Server Management Studio 2019. I change the login mode from mixed (Windows and SQL authentication) to only Windows and now I am not able to login with my sa account and I don't have another account which has admin rights.

What should I do? Any suggestion will be highly appreciated.

Improve this question

2 Answers 2

Reset to default
11

There are two ways to handle this.

One option is to login to the windows machine as a local administrator, start SQL Server in single user mode (-m startup switch) and you will now be able to get into your SQL server as a sysadmin using Windows authentication.

The other option is to quite simply change back to mixed mode by modifying the registry in windows directly (not supported). On my machine, the registry path for a named instance (named "a") is: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.A\MSSQLServer. And the value for mixed authentication is LoginMode: 2.

Improve this answer
3
0

If you are able to access the server that SQL Server is hosted on then you can use your local Windows account to gain access and change the setting back. If you do not have that level of access, you could contact the administrators of the server and ask them to change it back for you.

Once you are on the server do the following to change the setting:

  • Open SQL Server Management Studio as you would normally
  • Log in using Windows Authentication.
  • Once logged in, right click the server and choose Properties.
  • Click the Security page.
  • Under Server Authentication select the SQL Server and Windows Authentication mode radio button.
  • Click OK to save your changes.

For more information, see the documentation for Change Server Authentication Mode

Improve this answer
7
  • I am sorry but I am new to this. I am the only person who is responsible for this. I have created the server using windows server express or something like that. Now I am inside my local windows account, what should I do?
    – Nano
    Commented May 8, 2019 at 11:38
  • I have updated my answer with a guide.
    – Mr.Brownstone
    Commented May 8, 2019 at 11:46
  • Thanks for the answer and the update. However, it didn't work it kept giving me error about that I don't have permission. I was able to solve it by going to registry and change the login value from 1 to 2. Thanks a lot anyways
    – Nano
    Commented May 8, 2019 at 11:56
  • 1
    @Kashan editing the registry is risky and unsupported by Microsoft. But glad you got the system up. 😄 I would test bounce it at least once to see if your registry edit has any other effects and to be on the safe side!
    – clifton_h
    Commented May 8, 2019 at 12:45
  • 1
    @Learning_DBAdmin impossible if no one can access the server.
    – clifton_h
    Commented May 8, 2019 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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