Problem:
I recently changed my PC name. But the login in SQL Server still uses the old pc name. I decided to delete the login. Bad for me, the deleted login was the only one with sysadmin rights in SQL Server. The sa was not enabled. No other sysadmin logins. So I was completely locked out off the box. I cannot be back to SQL Server anymore with any existing logins.
Trial:
I switched to other windows logins with admin rights. Unfortunately, although they log into SQL Server, but they did not have the sysadmin rights. So they cannot create new sysadmin logins or enable the sa account.
Solution:
1. Followed the steps in this article - http://www.mssqltips.com/sqlservertip/2465/how-to-connect-to-sql-server-if-you-are-completely-locked-out/
(1) stop the SQL Server service
(2) login as a single user
(3) use SQLCMD -E (yes, I logged into as a local windows admin)
(4) create a SQL Server login, add it to the sysadmin role.
2. Start the SQL Server service again
(1) get an error for time out using the local system account when starting the service
(2) try newly created SQL server login to start the SQL Server service, wow, it worked!
3. Login to SQL server with the new login created in the SQLCMD
4. create a login based on the Windows login with the correct PC name (a windows admin) with sysadmin role
5. Login into SQL Server in Windows authentication, it worked!
Lessons Learned:
Pause, think, backup, test before making any changes in SQL Server. A seemly trivial change may eat up hours of your time if not done appropriately.
