create sql server login for own domain account

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.

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.'"


StackExchange Database Administrators Q#201655, answer score: 3