When no one have sysadmin permission in the sql instance including you, what to do?

Recently I came across a situation where after a server migration when I logged into the SQL instance I cannot see the SQL Server agent. Verified the registry location of the SQL server agent and it has the correct location. Verified folder permissions and the SQL service account have all the required permissions on all the required folders. The server has 2 SQL instances and only one instance is missing the SQL server agent so impulsively I have restarted the SQL services and obviously, it didn’t help.

After verifying realized my account itself doesn’t have sysadmin permission on the instance. Then noticed none of the accounts in the instance have sysadmin permission and even ‘sa’ account details are lost.

So the situation is no login has sysadmin permission in the SQL instance below are the steps I followed to overcome this issue.

** Please make sure You must be a member server’s local administrator's group before performing these steps.

1.    Open SQL server configuration manager and open the properties of SQL server service of the instance you have this issue. In my case, the instance name is SQL2017.

2.    Now click on startup parameters.

3.    Now type –m in Add startup parameters section and click on Add startup parameters.

4.    Now restart the SQL services of the instance. Now the SQL instance will be started in single-user mode. Which allows any one member of the server’s local administrator to connect to the SQL instance as a sysadmin.

5.    Open SSMS and login to the instance with your windows login.

6.    Now grant sysadmin permission to your account.

7.    Go back to SQL server configuration manager and remove the –m startup parameter. By selecting the -m parameter and click on Remove.

8.    Restart the SQL services.

9.    Now you can log in back to SQL instance using ssms and your account will have sysadmin permission.

 The same can be achieved via SQLCMD as well.

 Please share how you fixed this issue in your environment in the comments section below.

 

Thanks VV!!

 

#backtobasics #sqlserver #mssqlserver #mssql #singleusermode #administrator