Yesterday in one of the SQL forums there was an issue which was reported by one of the user.The question was something like –

‘I accidentally deleted the only Login which SSMS uses to connect to Database Engine.The server doesn’t have SQL Authentication.How do I connect now? Do I need to do a Uninstall and redo the whole thing?’

Solution – The answer to this is you do not have to uninstall the whole instance and the steps to come out of this trouble is mentioned below :

Members of Windows Administrative group have sysadmin privilege in SQL Server if you start SQL Server in Single user mode.Try the below steps.

1.Add your domain login as Server Admin Group(OS level)

2. Stop SQL Server  using the command, NET STOP MSSQLSERVER if its default instance,and if its named instance,then check the below mentioned article for more syntax.

http://technet.microsoft.com/en-us/library/ms190236.aspx

3.Start SQL Server in single user using the command   NET START MSSQLSERVER /m if its default instance,and if its named instance,then check the below mentioned article for more syntax.

http://technet.microsoft.com/en-us/library/ms187598.aspx

4. Login using SSMS and your domain ID.

5. You will get an error after opening New Query as SQL will allow only single connection.

6.Disconnect and close object explorer(Important step) and click New Query again.

7. Now you are connected and you can create a login using TSQL scripts and assign sysadmin.

So next time when you find this problem,you have a proven solution available.

Do drop in a comment in-case any of the above mentioned steps is not working for you.

Thanks for reading.