Creating a System Admin Login for SQL Server Using the Command Line

SQL Server logo

This past week I installed Microsoft SQL Server 2014 Developer Edition on my dev box, and immediately ran into a problem I had never seen before.

I’ve installed various versions of SQL Server countless times, and it is usually a painless procedure. Install the server, run the Management Console, it’s that simple. However, after completing this installation, when I tried to log in to the server using SSMS, I got an error like the one below:

SQL Server login error 18456
“Login failed for user… (Microsoft SQL Server, Error: 18456)”

I’m used to seeing this error if I typed the wrong password when logging in – but that’s only if I’m using mixed mode (Windows and SQL Authentication). In this case, the server was set up with Windows Authentication only, and the user account was my own. I’m still not sure why it didn’t add my user to the SYSADMIN role during setup; perhaps I missed a step and forgot to add it. At any rate, not all hope was lost.

The way to fix this, if you cannot log on with any other account to SQL Server, is to add your network login through a command line interface. For this to work, you need to be an Administrator on Windows for the PC that you’re logged onto.

1. Stop the MSSQL service.

2. Open a Command Prompt using Run As Administrator.

3. Change to the folder that holds the SQL Server EXE file; the default for SQL Server 2014 is “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn”.

4. Run the following command: “sqlservr.exe –m”. This will start SQL Server in single-user mode.

5. While leaving this Command Prompt open, open another one, repeating steps 2 and 3.

6. In the second Command Prompt window, run “SQLCMD –S Server_Name\Instance_Name”

In this window, run the following lines, pressing Enter after each one:

>CREATE LOGIN [domainName\loginName] FROM WINDOWS 
>GO 
>SP_ADDSRVROLEMEMBER 'LOGIN_NAME','SYSADMIN' 
>GO

7. Use CTRL+C to end both processes in the Command Prompt windows; you will be prompted to press Y to end the SQL Server process.

8. Restart the MSSQL service.

That’s it! You should now be able to log in using your network login.