MSSQL - How to start your SQL Server instance after disabling NT AUTHORITY login

This is one of the critical and bad situations that any DBA can face, to find yourself unable to start your SQL instance after disabling NT AUTHORITY login “for any reason”.

In my case it was disabled by the Network administrator guy by mistake and I did not realize that till restarting my SQL production instance for a planned maintenance operation!!

So, the situation is very critical that you find your production instance failed to be started with error message:

“Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: The account is disabled”.

The first positive point here is that the error message is clear enough and then we can start troubleshooting in the right track.

What we need to do in this situation is simply grant “sysadmin” permission for NT AUTHORITY system account again but the question is how can we connect to the instance while it is stopped?, the answer is to start it in single user mode and to do that we need to go through the following steps:

1- Open command prompt in administrator mode.

2- Navigate in SQL Server installation media till “Binn” folder.

3- Start SQL Server DB Engine service in single user mode using command “sqlservr.exe -s SQL2012 –m” while SQL2012 is the instance name, here is a complete example: 

“C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn>sqlservr.exe -s SQL2012 –m” 

while "–s  SQL2012" is the instance name and "–m" is the option for starting the instance in single user mode.

The next step then is to grant the NT AUTHORITY system user "sysadmin" permission and to do that we need to connect to the instance after starting it in single user mode via command prompt, the command to do that is as below:

SQL Authentication:

SQLCMD -S instance IP,port -U SQL account -P password

ALTER LOGIN [NT AUTHORITY\SYSTEM] ENABLE

GO

GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]

GO

  

Windows Authentication:

SQLCMD -S instance IP,port –E

ALTER LOGIN [NT AUTHORITY\SYSTEM] ENABLE

GO

GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]

GO

After that we can close the two opened command prompt and then restarting SQL Server instance in the normal mode.