Here is how to fix that:
1) Stop SQL Server
Open "Sql Server Configuration Manager" from the start menu. Select "SQL Server Services" from the left window pane. Right click each service and select "Stop".
2) Start a single-user instance of SQL Server
We will be working with two command windows. This will be command window "A".
Open a new command window and navigate to
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
Start SQL Server in "Single User Admin Mode" by running sqlservr with the -m flag:
sqlservr -m
3) Add the user account as an admin to the SQL Server instance
In a 2nd command window, Command window "B", navigate to the same directory
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
We will connect to the SQL Server instance we started above by using the sqlcmd tool with the -s flag to provide the server's name, in my case the "(local)" instance, and the -e flag to indicate we are connecting using windows authentication.
sqlcmd -s (local) -e
Now that we have connected, we can send commands directly to the SQL Server instance. We will add our user account to the sysadmin group as follows:
CREATE LOGIN [domain\username] FROM WINDOWS
GO
EXEC sp_addsrvrolemember @loginame='domain\username', @rolename='sysadmin'
GO
The account you substituted for domain\username will now be a sysadmin in the database.
4) Shut down your SQL Server instance and restart SQL Server
Execute the following commands:
Command Window B
shut down SQL Server:
shutdown
exit the command window:
exit
Command Window A
exit the command window:
exit
Now, switch back to Sql Server Configuration Manager and start each of the services you previously stopped.
You should now be able to log into the SQL server instance using SQL Server Management Studio, etc.
Have a great day :)
