Someone at client's site removed the builtin admin from SQL Server as it was one of the risks mentioned in the agreement .Everything was fine .
However,during maintenance SQL server did not come up after the server reboot .They tried it with SA but they also forgot the SA pasword :-) ....awesome ...
They were about to uninstall and reinstall SQL server when we finally did the steps below .It took me a lot of time but one of the options worked (with my previous MS experience ofcourse :) ).Below are the repro steps and the solution .
Repro of issue :
1) Delete the Builtin/administrator account .
2) tested it through sqlcmd and got the error 18456 Level 14 State 1.
3) assume that i have forgotten the SA password as well.
Solution:
1) Stop SQL Server service and start it with -m
2) go to C:\Program Files\Microsoft SQL Server\90\Tools\Binn through cmd prompt
3) type sqlcmd -E and hit enter .If its named instance then sqlcmd -
-SServer\instance -E and hit enter.
4) you will get > sign
5) commands you need to use
use master
go
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=
[master], DEFAULT_LANGUAGE=[us_english]
go
6) You are done.Exit out of it .
7) restart you SQL Server service without -m parameter.
Happy learning .....
1 comment:
Good to know.
Post a Comment