create sql server login for own domain account

Question

I have a working SQL Server instance to administer. I do not have a SQL Server login and do not know any other logins. I do have a Windows domain login to the server as a local admin. How can I add my Windows account as a login to SQL Server when I don't know any existing login to the instance?

I can connect using PowerShell:

cd sql\uk-blahblah\default\logins

but cannot retrieve any detail:

gci

SQL Server PowerShell provider error: Failed to read child items. [Failed to connect to server uk-blahblah. --> Login failed for user 'AUTOdomain\admin-account'.]

I can stop the SQL Service and restart from an elevated command prompt:

sqlservr.exe -m -c

but trying to run sqlcmd still fails:

sqlcmd -S UK-blahblah
Login failed for user 'AUTOdomain\admin-account'

I'm sure many of you must have found a way of adding your login to a server where you don't know any other logins, but I'm struggling a little here.

I'm using SQL Server 2012 SP2 Enterprise Edition.

Answer 1

There are several workarounds listed in the following articles:

• Recover Access to a SQL Server Instance
• More on Recovering Access to a SQL Server Instance

The more tedious workaround is detailed in the latter article, and should be used with extreme caution on a production server (totally fine for your own workstation):

1. In the Services applet, stop the SQL Server VSS Writer service
2. Place a copy of SQLCMD.exe in the VSS Writer folder
3. Rename SqlWriter.exe to SqlWriter.exe.old
4. Rename SQLCMD.exe to SqlWriter.exe
5. Change the registry entry in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLWriter so that the path includes arguments such as -S.\instance -E -Q"ALTER SERVER ROLE sysadmin..." where you can add logins to the sysadmin role.
6. Start the service again. It will fail to start, but the ALTER command will have been sent to the server.
7. Delete SqlWriter.exe, rename SqlWriter.exe.old back to SqlWriter.exe, and start the service again.

Answer 2

This requires stopping and starting the SQL Service, but will get you access to the SQL Server Instance if you are a local admin on the server.

Run this from an elevated command prompt.

Substitute the service name for mssqlserver if you have a named instance or SQL Express i.e. mssql$sqlexpress

Substitute your domain and account name for BUILTIN\administrators if you do not want to grant sysadmin for all local administrators and want just your account.

Note: the /m SQLCMD switch is single user mode and only a SQLCMD connection will be accepted. This is to keep from being locked out by a service that connects before I can get connected.

I have put these commands in a .bat file and ran it from an elevated command prompt. That makes the outage very short.

As always, test on a dev instance before you go running against production.

net stop mssqlserver

net start mssqlserver /mSQLCMD

sqlcmd -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'" 

net stop mssqlserver 

net start mssqlserver 

sqlcmd -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"