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.
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
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE= [master], DEFAULT_LANGUAGE=[us_english] go
6) Give sysadmin role to the login we just created
EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin' GO
7) You are done.Exit out of it .8) restart you SQL Server service without -m parameter.
You are done .Login again to SQL Server and reset the SA password .Save the SA password for the rainy day .
Happy learning !