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
  • 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

Reference 

This entry was posted in IT and tagged . Bookmark the permalink.