Tuesday 12 November 2013

MS SQL Server 2012: I forgot to add an administrator account

Maybe you had previously the similar problem. You were installing MS SQL Server 2012 and you forgot - missed - or your choice was not correct - to add an administrator account to administer your new SQL Server. I had another problem - I've added Administrators group, not an administrator user. I hoped that it will work for all users in that proper group. Unfortunatelly not.
So I searched google for some solution. Of course, I found a lot of articles about creating new login with sa privileges.
E.g. http://www.practicalsqldba.com/2012/08/sql-server-lost-all-administrator.html

But due to a higher security I've chosen the Windows authentification only. My newly created SQL account didn't work. I had to enable the Mixed authentification on my SQL instance. And I want to share this guide because when I searched it on google I was not successful.

1. Run regedit
2. Locate MSSQL setup

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.'YOUR INSTANCE NAME'\MSSQLServer

3. Modify LoginMode key to value 2.
It means 1 for the Windows authentification, 2 for the Mixed one.

You have to restart your database service.

Then create new login for your windows user, assign him proper role and switch LoginMode back to the Windows authentification.

No comments:

Post a Comment