Lost your SYSADMIN Privilege to SQL Instance??

This is what happened to me today.
My login ID is the only account in SQL Instance and it has sysadmin privilege. While exploring something, I had a doubt what if I dropped my own login account? Will SQL allow me to do that?
Okay let’s try this

Query executed successfully which means the only login account we had is dropped now.

As I deleted my login account now my connection should be closed. I went and refreshed the Logins (Under Security in Management Studio) and this is how it was. Only SA account is visible.

   

Now we are only left with SA account, which will not be helpful now since my instance is configured with Windows Authentication. Though it won’t work, just simply tried to connect and ended up this

My bad, DAC account is also not enabled for my instance. So that option is ruled out.

Hmm.. What next?

Now we have to start SQL with single user mode and see if it can help us. 

First let’s stop the SQL service and start SQL instance in single user mode using command prompt. To start SQL instance in single user mode, we have to use /m at the end along with SQLCMD. In this case my command will be like

Net start MSSQL$SQL2012 /mSQLCMD

Now let’s try to connect to the instance.

Great, now we are able to connect. All we have to do now is create the required login and provide the permission.

 

Now we’ll restart the SQL instance (now single user mode is not required) and let’s try to connect. Hopefully it should work now, as we have created the login with sysadmin rights.

Perfect!! Now things are back to normal.

 

 

Note: To perform this task you should be a me mber of Administrator group in Windows level. If not, this will not work. Because when you start SQL in single user mode, your authentication will happened from Windows AD.

Thanks for reading this post. Hope you find this useful.

Cheers,

Naveen