Hi Guys ,

A few days ago someone at client’s site removed the builtin administrator from SQL Server as it was one of the risks mentioned in the agreement .Everything was fine and no one realized what mistake has been done.

Things were fine till the time it was needed to login to SQL Server .No one saved SA password either and een though the SA login was enabled , we were not able to use it :)….awesome …
They were about to uninstall and reinstall SQL server when we finally could resolve the issue by following the steps below .It took us a lot of time but one of the options worked.Below are the steps to reproduce this issue and the solution .Make sure you have SA password saved before attempting this .

Repro of issue :

1) Delete the Builtin/administrator account .
2) Try to connect to SQL Server through sqlcmd or Management studio .You will  get the error 18456 Level 14 State 1.
3) Assume that i have forgotten the SA password as well.

Solution:
1) Login to the server using administrator account .

2) Stop SQL Server service and start it with -m switch(single user mode) .

2) Type sqlcmd -E and hit enter .If its named instance then sqlcmd -S <Server\instance> -E and hit enter.

4) You will see > sign

5) Commands you need to use

6) Give sysadmin role to the login we just created

7) You are done.Exit out of it .8) restart you SQL Server service without -m parameter.

1_Recreating_Builtinadministrator_account_in_SQL_Server_(2000_and_above)

You are done .Login again to SQL Server and reset the SA password .Save the SA password for the rainy day .

Happy learning !

Regards

Abhay Chaudhary

Like us on FaceBook Follow us on Twitter

Follow me on FaceBook | Join the fastest growing SQL Server group on FaceBook