Sql-server – How to unlock sa user in SQL Server 2012 if Windows Authentication doesn’t work

Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out:

Resolution

Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.

To start the SQL with -m (from a console running as administrator):

net stop mssqlserver
cd "c:\program files\Microsoft SQL Server\...\MSSQL\binn"
sqlservr -c -m

Or, if you have a named instance:

net stop mssql$<INSTANCENAME>
cd "c:\program files\Microsoft SQL Server\...\MSSQL\binn"
sqlservr -c -m -s <INSTANCENAME>

There are alternative means as shown in How to: Configure Server Startup Options, but I always preffer start from command prompt for something like this. After you start the instance with -m:

1. Open with SSMS using Run as Administrator
2. At the first prompt don't click connect but say cancel
3. Open a new query window and then connect to the database with that query window

4. Now add yourself to the SQL sysadmin group

   create login [domain\you] from windows;

   exec sp_addsrvrolemember 'domain\you', 'sysadmin';

Now shutdown your SQL Server and restart it normally (start back the service). domain\you is now a sysadmin on the server and can administer it. You don't need to run SSMS as administrator.

That's certainly how I would've done it - though I always avoid using local users for anything. In my mind, local logins are to get you out of trouble.

Personally, I would have installed everything with a domain admin account and then just added the "Domain Admins" security group.

Or, even better, create a service account.