Allow Domain Admin to Login to SQLServer & have full Access
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 (at the end)
Click Apply. Restart the Service
Add Windows User as SQLServer Admin
Start a Command Prompt (right-click Run as Administrator!!)
Issue the following commands:
1> exec sp_addsrvrolemember ‘domain\schalley’, ‘sysadmin’;
#For a named instance, go:
sqlcmd -E -S servername\instancename
sqlcmd -E -S 127.0.0.1,1488 (port:1488)