Recently, I ran into a scenario where a corrupt Active Directory had been rebuilt, however the SQL server logins got lost in the shuffle. Consequently, there was no way to log into the SQL server except for the sa account, and that password was long-forgotten. Fortunately, there’s a way to fix this problem without reinstalling SQL and reattaching the databases.
Microsoft SQL Server has the ability to launch in Single-User Mode. In this mode, any account that is a member of the local Administrators group will be able to log in to the server with sysadmin privileges. To launch in Single-User Mode, one must use a startup parameter for the SQL instance in question.
While in Single User Mode, only one user can be connected at a time (as the name would imply). You’ll connect and interact with the SQL instance from a command prompt using SQLCMD commands.
1. Open an elevated Command Prompt.
net stop MSSQLSERVER
3. Start the SQL Instance using the ‘/m’ switch and specifying you’ll use SQLCMD to interact with the instance. The input following the ‘/m’ switch is case-sensitive. There’s no indication you’re connected in Single-User Mode, so don’t worry if you don’t see anything.
net start MSSQLSERVER /m"SQLCMD"
4. Connect to the instance with SQLCMD. Just type ‘sqlcmd’ and hit <ENTER>. You’ll find yourself at a numbered prompt. This means you’re connected to the default instance. If you want to specify a particular SQL instance, just use the appropriate SQLCMD switches. The syntax will be:
5. From here, you use Transact-SQL (T-SQL) commands to create a login. I’m going to create a login called “RecoveryAcct” and give it the password “TempPass!”. Since you’re issuing T-SQL commands directly, you’ll need to use the ‘GO’ command, too. There’s no indication the command was successful; you just end up back at a ‘1>’ prompt. If you don’t get an error, you can assume all is well.
CREATE LOGIN RecoveryAcct WITH PASSWORD=’TempPass!’ GO
6. Now, use more T-SQL commands to add the user to the SysAdmin role. Again, you’ll need to use the ‘GO’ command and if you don’t get an error, you can assume all is well.
SP_ADDSRVROLEMEMBER RecoveryAcct,’SYSADMIN’ GO
net stop MSSQLSERVER && net start MSSQLSERVER
You can also use the SQL Server Management Studio if you’re not comfortable with SQLCMD. When you are starting the instance in Single-User Mode, you can always specify “Microsoft SQL Server Management Studio” instead of “SQLCMD” after the ‘/m’ switch in step 3.
net start MSSQLSERVER /m"Microsoft SQL Server Management Studio" GO
I hope this helps you out.