Locked out of SQL Server? Forgot the SA password? How to create a new SQL Server administrator.

Posted by Neil Moffatt - 30 November, 2012

header-picture

If you have every found yourself locked out of SQL Server 2008 R2 it can be quite a scary experience. This scenario does happen from time to time even with our best intentions to follow Microsoft best practices. Over time accounts can be removed from Active directory that were once administrators or maybe we simply have forgotten the SA password when SQL was installed.

Don't panic, no need to try and hack into SQL as Microsoft has given us a back door to access SQL and create ourselves a new administrator.

The first thing we need to do is notify our users that the systems that utilize that particular SQL Server will be down for a bit while we fix our little administration problem (Assuming that they are still up and running!).

Once this is done we need to log into the SQL Server as an administrator using a local account on the server. This is very important as the procedure below will not work with a domain user that has local administrator rights on the server. If possible just use the '[server name]\administrator' account.

Now you can begin by stopping the SQL Server and the SQL Server Agent services from the SQL Server Configuration Manager:

SQL Server 2008 R2 - Services

Then we need to re-start the SQL Server service in single user mode. The easiest way is to modify the start-up parameters on the advanced tab from the SQL Server Configuration utility as shown below (Don't forget the semi-colon or add and spaces!). To access the parameters, right click on the SQL Server service in configuration manager and select 'Properties' then click on the 'Advanced' tab.

 

Adding single user mode

Once the parameter has been modified, start the service from the configuration manager. The SQLCMD utility can be used as well (SQLCMD -S [server/instance] -m).

This will give us access to the backdoor Microsoft has provided. When the database server is running in single user mode a local administrator will be able to open Management Studio and will automatically be logged in as a server administrator.

Now we will be able to create a new administrator or add the server role to an existing login.

Phew! Crisis over!

Once the new administrator has been set up, remove the single user start-up parameter and restart the service and you should be back in business.

Topics: Blog


Recent Posts

What is Azure Cosmos DB?

read more

InCycle Three-peats at 2019 Microsoft Partner Awards

read more

InCycle Sponsors Azure DevFest

read more