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)
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
2> ALTER SERVER ROLE [sysadmin]
3> ADD MEMBER [NewAccount]
To Reset password of an existing account use below code:
1> ALTER LOGIN [ExistingAccount] WITH PASSWORD = N'Welcome'
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!
Filed under: Management Studio, SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012