If you have lost the password for sa account or does not have any administrative account and you are locked out of SQL Server you can still login to SQL Server as an Administrator using Local administrator account.

You can use any account which is part of local Administrators group. To be able to login using local admin account you need to stop and start SQL Server in single user mode.

Using below method you can restore access to your server by resetting forgotten administrator password or by creating a new Administrative account in SQL Server.

We need to perform below steps to create new administrator account in SQL Server

Step 1. Stop SQL Server from Services Console or SQL Server Configuration Manager

Step 2. Start SQL Server in single user mode using -m startup parameter

You can add -m switch as a startup parameter using SQL Server Configuration Manager or You can start SQL Server in single user mode from command prompt using below command:

C:\> sc start MSSQLSERVER -m

Replace MSSQLSERVER with service name on your system

Before you start SQL Server in single user mode make sure SQL Server Agent is disabled

Step 3. Connect to SQL Server using SQLCMD or SQL Server Management Studio

To connect to SQL Server when running in single user mode using SQL Server Management Studio use "Database Engine Query" from File Menu > New option *

* You cannot connect using Object Explorer while SQL Server is running in single user mode as Object Explorer requires 2 separate connections to server

To connect to SQL Server using SQLCMD, run below on command prompt:

C:\>SQLCMD -S (local)

1>

Step 4. Create New Administrator account or reset password for an existing account

When connected to SQL Server using SQLCMD or SQL Server Management Studio execute below script to create a new administrator account:

1> CREATE LOGIN [NewAccount] WITH PASSWORD = N'Welcome',

2>        DEFAULT_DATABASE = [master],

3>        CHECK_EXPIRATION = OFF,

4>        CHECK_POLICY = OFF

5> GO

1>

2> ALTER SERVER ROLE [sysadmin]

3> ADD MEMBER [NewAccount]

4> GO

1>

To Reset password of an existing account use below code:

1> ALTER LOGIN [ExistingAccount] WITH PASSWORD = N'Welcome'

2> GO

Replace [ExistingAccount] with login name for which you want to reset the password, and replace 'Welcome' with new password, make sure new password meets the security policies

Step 5. Stop SQL Server Service using Services Console or SQL Server Configuration Manager

Step 6. Start SQL Server Service normally using Services Console or SQL Server Configuration Manager

Now you can login to SQL Server using the newly created Administrator account.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Filed under: Management Studio, SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012