For some reason when SQLServer 2008 was installed/setup, admins were not added. Therefore when trying to login via SQL Management Studio, an error was obtained:
Login failed for user "username". (Microsoft SQL Server, Error: 18456)
I also didn’t know the “sa” password which would have let me login. I also didn’t have access to another admin account to test logging in under their account.
Start SQLServer in single-user mode
- Open “SQL Server Configuration Manager” found in the Start Menu under Microsoft SQL Server 2008 R2 -> Configuration Tools.
- Right-Click properties of the SQL instance you want to start in single user mode.
- (Optional / Maybe not required) Change the Logon Account to a different one
- Under Advanced and “Startup Parameters”, add in:
;-m - Click Apply. Restart the Service
Add Windows User as SQLServer Admin
- Start a Command Prompt (right-click Run as Administrator!!)
- Issue the following commands:
C:\Windows\system32>sqlcmd -E 1> exec sp_addsrvrolemember 'yourdomain\sburke', 'sysadmin'; 2> go 1> exit
#For a named instance, go: sqlcmd -E -S servername\instancename
