Allow Domain Admin to Login to SQLServer & have full Access

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:

C:\Windows\system32>sqlcmd -E
1> exec sp_addsrvrolemember ‘domain\schalley’, ‘sysadmin’;
2> go
1> exit

#For a named instance, go:
sqlcmd -E -S servername\instancename
or
sqlcmd -E -S 127.0.0.1,1488     (port:1488)

Leave a Reply

Your email address will not be published. Required fields are marked *

fifteen − ten =

This site uses Akismet to reduce spam. Learn how your comment data is processed.