Search This Blog

Saturday, September 12, 2009

Recreating Builtin/administrator account in SQL Server 2005

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: