Sql-server – create sql server login for own domain account

loginssql serversql-server-2012

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.

Best Answer

There are several workarounds listed in the following articles:

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.
Related Question